tSQL summarise data by week based on date field?

Question

I'm looking to summarise data by week from a given starting date into the indefinite future. Typically I would just dump a result set into an Excel dash and group there via pivots etc., but I'm writing this query to hand off to a set of users and allow them access through a front-end BI application, so I want to give it to them as complete as possible.

My current method is obviously inefficient, so I'd love to find a simple formula to do this (or a way to create a variable for the week to cut back on all of the repetitive scripting). I've thought about using DATEADD but haven't been able to make it work yet.

Example of my current solution is below:

SELECT 
    'Week Ending' = (CASE 
                        WHEN YEAR(duedate) = '2015' 
                          THEN (CASE 
                                  WHEN datepart(wk, duedate) = 32 THEN '2015/08/07'
                                  WHEN datepart(wk, duedate) = 33 THEN '2015/08/14'
                                  WHEN datepart(wk, duedate) = 34 THEN '2015/08/21'
                                  WHEN datepart(wk, duedate) = 35 THEN '2015/08/28'
                                  WHEN datepart(wk, duedate) = 36 THEN '2015/09/04'
                                  WHEN datepart(wk, duedate) = 37 THEN '2015/09/11'
                                  WHEN datepart(wk, duedate) = 38 THEN '2015/09/18'
                                  WHEN datepart(wk, duedate) = 39 THEN '2015/09/25'
                                  WHEN datepart(wk, duedate) = 40 THEN '2015/10/02'
                                  WHEN datepart(wk, duedate) = 41 THEN '2015/10/09'
                                  WHEN datepart(wk, duedate) = 42 THEN '2015/10/16'
                                  WHEN datepart(wk, duedate) = 43 THEN '2015/10/23'
                                  WHEN datepart(wk, duedate) = 44 THEN '2015/10/30'
                                  WHEN datepart(wk, duedate) = 45 THEN '2015/11/06'
                                  WHEN datepart(wk, duedate) = 46 THEN '2015/11/13'
                                  WHEN datepart(wk, duedate) = 47 THEN '2015/11/20'
                                  WHEN datepart(wk, duedate) = 48 THEN '2015/11/27'
                                  WHEN datepart(wk, duedate) = 49 THEN '2015/12/04'
                                  WHEN datepart(wk, duedate) = 50 THEN '2015/12/11'
                                  WHEN datepart(wk, duedate) = 51 THEN '2015/12/18'
                                  WHEN datepart(wk, duedate) = 52 THEN '2015/12/25'
                                  WHEN datepart(wk, duedate) = 53 THEN '2016/01/01'
                                END)
                        WHEN YEAR(duedate) = '2016' 
                          THEN (CASE 
                                  WHEN datepart(wk, duedate) = 1 THEN '2016/01/01'
                                  WHEN datepart(wk, duedate) = 2 THEN '2016/01/08'
                                  WHEN datepart(wk, duedate) = 3 THEN '2016/01/15'
                                  WHEN datepart(wk, duedate) = 4 THEN '2016/01/22'
                                END)
                     END),
    SUM(linetotal) 
FROM 
    PurchaseOrderDetail
WHERE 
    YEAR(duedate) > '2014'
GROUP BY 
    YEAR(duedate), DATEPART(wk, duedate)

I imagine the answer is a simple one, but I haven't been able to find quite what I'm looking for yet.

Possible duplicate of [How to get first day of the week and last day of the week in sql server 2008?](http://stackoverflow.com/questions/21330671/how-to-get-first-day-of-the-week-and-last-day-of-the-week-in-sql-server-2008) — Tab Alleman, Dec 28 '15 at 20:00

score 0 · Answer 1 · answered Dec 28 '15 at 20:36

Consider a solution along these lines (please note - the code is untested as I do not have access to a SQL Server instance right now). The idea is to replace the (hard-coded) date with year and week numbers. This suggested solution will allow your BI users to view the SUM of linetotal by year and week number. As you get more data, users can even compare the weekly linetotal by year (linetotal for 2014, 2015, 2016, ..).

SELECT
    YEAR(duedate) AS Year1, datepart(wk, duedate), SUM(linetotal)
FROM 
    PurchaseOrderDetail
WHERE 
    YEAR(duedate) > '2014'
GROUP BY 
    YEAR(duedate), DATEPART(wk, duedate)

CactusCake · Accepted Answer · 2015-12-28T21:19:22.530

Something like this will give you the same output without having to manually specify every week ending date:

SELECT
convert(varchar, duedate - datepart(dw, duedate) + 6, 111) as [Week Ending],
sum(linetotal) 

FROM 
PurchaseOrderDetail

WHERE 
year(duedate) > '2014'

GROUP BY 
year(duedate), convert(varchar, duedate - datepart(dw, duedate) + 6, 111)

Converting to varchar (style 111) is not absolutely necessary, but it will give the same 'YYYY/MM/DD' format that you had in your original case statement. You could just as easily use duedate - datepart(dw, duedate) + 6 without the convert function, which will output the same value, but use the same datetime/datetime2/smalldatetime format as your input (probably 'YYYY-MM-DD HH:MM:SS').

score 0 · Answer 3 · answered Dec 28 '15 at 21:17

The function you are looking for could be this :

SELECT 
    'Week Ending' = dateadd( wk,
                             datepart(wk,duedate)-1, 
                             DATEADD(DAY, 
                                     6 - DATEPART(WEEKDAY, DATEADD(YEAR, year(duedate) - 1900, 0)), 
                                     DATEADD(YEAR, year(duedate) - 1900, 0)))

tSQL summarise data by week based on date field?

3 Answers3