SQL Totals for this week and other useful time periods

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.