Grouping by Week Datepart, display the spanning dates e.g. "25/12/2015 - 31/12/2015"

Question

When using Datepart to group by week, is it possible to easily have it display the dates the weeks span. Below is an example of my sql:

SELECT 
'Week ' + cast(datepart(wk, Table.ApplicationDate) AS VARCHAR(2)) Week
,year(Table.ApplicationDate) Year 
,COUNT(Table.Value) AS [Applications]

FROM Table

GROUP BY datepart(wk, Table.ApplicationDate), year(GrantDetail.ApplicationDate)

Ideally I'd like to have Week 2 - 25/12/2015 - 31/12/2015

score 1 · Accepted Answer · answered Feb 02 '16 at 17:50

This will return the date ranges you are looking for. Note that using Min and Max only works if there are reliably entries for every possible day.

select 'Week ' + cast(datepart(wk, Table.ApplicationDate) as varchar (2)) 
        + ' - ' 
        + convert(varchar(8), dateadd(wk, datepart(wk, Table.ApplicationDate) - 1, '1-1-' + cast(datepart(YEAR, Table.ApplicationDate) as varchar)), 3) 
        + ' - ' 
        + convert(varchar(8), dateadd(wk, datepart(wk, Table.ApplicationDate), '1-1-' + cast(datepart(YEAR, Table.ApplicationDate) as varchar)) - 1, 3)

You take January 1 of the year in question, then add the number of weeks (minus 1) you've calculated to that date to get the beginning of the week. Then add one more week, minus one day, to get the end of the week.

Edit: noticed you are using DD/MM rather than MM/DD, so edited my code to convert to the correct format.

score 0 · Answer 2 · edited May 23 '17 at 12:15

If you can assume that all your dates are covered within the data then you can use min and max, otherwise you'll need to calculate them:

dateadd(dd, -datepart(dw, min(ApplicationDate)) + 1) as StartOfWeek,
dateadd(dd, -datepart(dw, min(ApplicationDate)) + 7) as EndOfWeek

I'm assuming the your datefirst settings corresponds to the logical week you want to use as you're already relying on that. Also double-check the documentation for datepart(wk, ...) to confirm it does what you're expecting especially for dates around the beginning and end of the year. (See the accepted answer at Difficulties comprehending ISO_week and week in Microsoft SQL, what's happening exactly?)

Some people prefer to avoid the old datepart codes so here are the equivalents:

dateadd(day, -datepart(weekday, min(ApplicationDate)) + 1) as StartOfWeek,
dateadd(day, -datepart(weekday, min(ApplicationDate)) + 7) as EndOfWeek

You may find it better to group on a count of the number of weeks since a fixed reference point. January 1, 2012 happened to be a Sunday so I'll use it. All of the same logic above will still work and it doesn't really matter if any of your data falls before that date since the value of the expression will just be a negative number:

group by datediff(wk, '20120101', ApplicationDate)

Grouping by Week Datepart, display the spanning dates e.g. "25/12/2015 - 31/12/2015"

2 Answers2