[dba-SQLServer] If or Case or 'what'?

Mark A Matte markamatte at hotmail.com
Mon Jun 19 12:26:41 CDT 2006


Hello All,

I need a little help on some SQL.  I am counting and summing some stuff.  
This is a very large recordset(80m I think)...and I would like to do this in 
one pass if possible.  Here is what I have:
***
SELECT Cus_Customer_ID as Customer_ID,
	count(Hdr_Transaction_Date) as Frequency,
	sum(Hdr_Sale_Amount) as Monetary
FROM dbo.Customers, dbo.Headers
WHERE Cus_Customer_ID = Hdr_Customer_Number AND
	Hdr_Transaction_Date BETWEEN '06/04/2004' AND '06/03/2006'
GROUP BY Cus_Customer_ID
***

I need to have the 'Frequency' and 'Monetary' fields split by 12 month 
periods.  So I would end up with 1 row for each Customer with the following 
Frequency12, Monetary12, Frequency24, Monetary24.  Currently they use 2 
passes using temp tables to get this and then join the temps.  What would be 
the fastest way of getting this in a single SQL statement ( and the syntax)?

Thanks,

Mark A. Matte





More information about the dba-SQLServer mailing list