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:

         AccountNumber = X and InvoiceDate >= '10/1/2012 and <= '10/31/2012'

Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
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


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...I have it working now.  I will have to do something different for the count.

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.