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

Thanks Stuart,

This is exactly what I need.  I stripped down the original query to give 
this example.  Both tables are used in the original query.

In your opinion...if you had to get this type of info from 80 mil+ 
recordset...as far as speed goes...would you go multiple passes into temp 
tables and join later, or case statements?

Thanks,

Mark A. Matte


>From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer] If or Case or 'what'?
>Date: Tue, 20 Jun 2006 07:32:55 +1000
>
>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
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>