Link to home
Start Free TrialLog in
Avatar of Jimbo99999
Jimbo99999Flag for United States of America

asked on

SQL Query - Getting Monthly and YTD Totals

Good Day Experts!

I am trying to minimize trips and time to the db from my VB.Net program since the table I need to query is quite large.  For a query, I am looking to get monthly and yearly totals for a given let's say AccountNumber. I can't wrap my arms around how to get yearly total while specifying the monthly date range for the monthly total.  Is it possible to do:

Select
          AccountNumber,
          Sum([MonthlyTotal]),
          Sum([YearlyTotal])
From
          InvoiceTable
Where
         AccountNumber = X and InvoiceDate >= '10/1/2012 and <= '10/31/2012'

Thanks,
jimbo99999
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The key to speed is how the table is clustered (or that you have a properly keyed noclustered covering index).

For example, if the table is clustered/indexed by ( AccountNumber, InvoiceDate ), then such totals can be done very quickly, no matter the size of the table.
I think you need to do all the monthly sums and then sum that to get the yearly sum or run a separate query for yearly sum with a different (full year) date range.
Don't forget your GROUP BY clause :)
Avatar of Jimbo99999

ASKER

Good Day Experts!

I have tried the CASE usage in the select like ScottPletcher suggested.  It is works great for when I want to SUM the "Monthly Total"  and still have the "Yearly Total".

I forgot that I need to do the same for COUNT(DISTINCT [PRO NUMBER]) as mycount in my seperated queries for "Monthly Total" and "Yearly Total"...which works great I get what I need.

Now, when I try to put this in the query getting both Monthly and Yearly Totals, I cannot get the syntax right with the CASE. Here is my attempt so far:

Count(CASE WHEN InvoiceDate >= '9/1/2012' AND InvoiceDate < '9/29/2012' THEN DISTINCT [PRO NUMBER] ELSE 0 END)

I get an error indicating incorrect syntax near DISTINCT.

What do you think?

Thanks,
jimbo99999
Thanks...I have it working now.  I will have to do something different for the count.

jimbo99999
Count(DISTINCT CASE WHEN InvoiceDate >= '9/1/2012' AND InvoiceDate < '9/29/2012' THEN [PRO NUMBER] ELSE 0 END)


[I'm on vacation/holiday today and tomorrow, so I may not post again until Thurs.]
The mm/d[d]/yyyy format is not 100% safe for dates -- it depends on SQL settings -- and can be ambiguous at times.

'YYYYMMDD' is always interpreted correctly by SQL.