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