Group by ISO Week and year

Asked Jan 18 '16 at 13:55

Active Jan 18 '16 at 13:55

Viewed 370 times

I have the following:

Date            Value
2015-12-28      5       //WK 53 2015
2016-01-01      10      //WK 53 2015
2016-01-04      4       //WK 1 2016

I try to group the data by iso week and get the avrage value of each week with the folloing query:

SELECT CONCAT(DATEPART(ISO_WEEK, Date),';', YEAR(Date)) AS 'Date', ROUND(AVG(Value), 2) AS 'Value' 
FROM table 
GROUP BY DATEPART(ISO_WEEK, Date), YEAR(Date)

The problem is that with this group by, 2016-01-01 is treated as week 53 of 2016 instead of 2015, giving me the wrong data.

Is there an easy fix to correctly group by the correct week?

Also a side question: Is it possible to display the first day of the week instead of the CONCAT I have?

asked Jan 18 '16 at 13:55

Hauba

sounds like this might help http://stackoverflow.com/questions/22829604/what-is-iso-year-in-sql-server get you to the answer – Matt McCabe Jan 18 '16 at 14:00
first day of the week(monday) dateadd(day, datediff(day, 0, getdate())/7*7, 0) – t-clausen.dk Jan 18 '16 at 14:00
@MattMcCabe The anwser in the post did the trick, thank you. – Hauba Jan 18 '16 at 15:02
@t-clausen.dk Thank you, it works perfectly. – Hauba Jan 18 '16 at 15:03
@Hauba posted it long ago, saw possible improvement, so the answer has been changed a bit now. Still giving same result – t-clausen.dk Jan 18 '16 at 15:09

Group by ISO Week and year

0 Answers0