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

First thing that strikes me is, why include dboCustomers, looks like you 
are only using Customer_ID and this is in dboHeaders as  Customer_Number. 

Don't know about fastest but one approach would be something like

Declare @EndDate DateTime
Set @EndDate - '06/03/2006'

Select Distinct  Hdr_Customer_Number,
sum ( Case Hdr_Transaction_Date 
         When  > @Date - 365 Then 1
         Else 0
  End) as Frequency12,

sum (Case Hdr_Transaction_Date 
         When  > @Date - 365 Then Hdr_Sale_Amount
         Else 0
   End ) as Monetary12,
            
Count(*) as Frequency24,
Sum(Hdr_Sales_Amount*) as Monetary24

Where Hdr_Transaction_Date > @Date - 730
GROUP BY Cus_Customer_ID

On 19 Jun 2006 at 17:26, Mark A Matte wrote:

> 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)?
> 
-- 
Stuart