Introduction
Twice this month I have answered questions about getting totals from an SQL database for this week, and this year - so I thought I'd just make it easier to find.
You don't need to use this if you are only interested in a total for a single period: it's a lot more efficient to use a WHERE clause to restrict the rows you are checking to just the ones you want. But, if you need to return the totals for each sales droid by year, month and week in the same query, then this is the way to go.
Using the code
SELECT SUM(Sales) AS TotalSales,
SUM(CASE WHEN DATEDIFF( yy, SaleDate, GETDATE() ) = 1 THEN Sales ELSE 0 END) AS SalesLastYear,
SUM(CASE WHEN DATEDIFF( m, SaleDate, GETDATE() ) = 1 THEN Sales ELSE 0 END) AS SalesLastMonth,
SUM(CASE WHEN DATEDIFF( ww, SaleDate, GETDATE() ) = 1 THEN Sales ELSE 0 END) AS SalesLastWeek,
SUM(CASE WHEN DATEDIFF( yy, SaleDate, GETDATE() ) = 0 THEN Sales ELSE 0 END) AS SalesThisYear,
SUM(CASE WHEN DATEDIFF( m, SaleDate, GETDATE() ) = 0 THEN Sales ELSE 0 END) AS SalesThisMonth,
SUM(CASE WHEN DATEDIFF( ww, SaleDate, GETDATE() ) = 0 THEN Sales ELSE 0 END) AS SalesThisWeek,
SUM(CASE WHEN DATEDIFF( d, SaleDate, GETDATE() ) = 0 THEN Sales ELSE 0 END) AS SalesToday
FROM MyTable
They all work the same way:
DATEDIFF returns the difference between the current date and the row date in the appropriate form:
yy returns the number of years
m returns the number of months
ww returns the the number of weeks
If the returned value is 0, then its the same year, month, or week. if it's 1, then it's the previous one.
SUM adds up values, so we put a CASE inside that, so each row SaleDate value is checked for the appropriate time period, and if it is inside, the SUM gets the number of sales. If it isn't the SUM gets zero.
Easy!
History
First version.
