Search code examples
sqldatetimegroup-bydatepart

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

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

Solution

  • 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.