Problem with grouping on european standard week and year

Question

There is a datepart iso_week returning the iso_week for a selected day.

SELECT DATEPART(iso_week, getdate())

This will return the european standard for week current. It is not nessasarily the same as week.

Here comes the tricky part:

SELECT DATEPART(iso_week, '2011-01-01')

Returns

so it belongs to last year In order to group by year and iso_week, I need to consider that iso_week is not from the same year.

Iso_week starts on monday and belongs to the year where most days overlap. So 4 days in 2010 and 3 days in 2011, the week is 52 and all days belong to iso_year 2010. However TSQL doesn't have the detepart iso_year.

declare @t table(col1 datetime)

insert @t 
ALL SELECT '2010-12-28'
UNION ALL SELECT '2010-12-29'
UNION ALL SELECT '2010-12-30'
UNION ALL SELECT '2010-12-31'
UNION ALL SELECT '2011-01-01'
UNION ALL SELECT '2011-01-02'
--UNION ALL SELECT '2011-01-03' Stackexchange is bugged and can't handle this line showing 53
UNION ALL SELECT '2011-01-04'

I need something like (iso_year doesn't exist):

SELECT DATEPART(ISO_WEEK, col1) WEEK, DATEPART(iso_YEAR, col1) YEAR, COUNT(*) COUNT 
FROM @t
GROUP BY DATEPART(ISO_WEEK, col1), DATEPART(iso_YEAR, col1)
ORDER BY  2,1

Expected result

WEEK  YEAR          COUNT
52    2010           6
1     2011           2

score 3 · Accepted Answer · edited May 23 '17 at 11:59

3

The same ISO week's Thursday will unambiguously provide you with the correct year. And this answer can provide you with the idea how to obtain the right Thursday from the given date.

SELECT
  Week  = DATEPART(ISOWK, TheThursday),
  Year  = DATEPART(YEAR, TheThursday),
  Count = COUNT(*)
FROM (
  SELECT
    TheThursday = DATEADD(
      DAY,
      3 - (DATEPART(DW, col1) + @@DATEFIRST - 2) % 7,
      col1
    )
  FROM @t
) s
GROUP BY
  TheThursday

edited May 23 '17 at 11:59

Community

1
1

answered Jul 20 '11 at 23:41

Andriy M

76,112
17
94
154

Very nice, took care of the problem without setting firstdate and took care of both higher and lower week breaks. Also describing the calculation with TheThursday. Take a peek at my version as well please. – t-clausen.dk Jul 21 '11 at 05:09

score 1 · Answer 2 · answered Jul 20 '11 at 19:55

You can check against dayofyear and iso_week to figure out if you need to subtract year with 1 or not.

select datepart(iso_week, col1) as [week], 
       case when datepart(dayofyear, col1) < 7 and
                 datepart(iso_week, col1) > 51
            then year(col1) - 1
            else year(col1)
       end as [year], 
       count(*) as [count]
from @t
group by datepart(iso_week, col1),
         case when datepart(dayofyear, col1) < 7 and
                   datepart(iso_week, col1) > 51
              then year(col1) - 1
              else year(col1)
         end
order by [year], [week]

It is a decent solution, but it is going to be a long statement when also taking care of dates like '2008-12-31' which is actually week 1. Andriy came up with a better solution. — t-clausen.dk, Jul 21 '11 at 05:20

Ben Tennen · Answer 3 · 2011-07-20T21:36:31.933

1

Edited...

SELECT 
  IsoWeek  = DATEPART(ISO_WEEK, TheDate), 
  IsoYear  = CASE 
    WHEN 
      MONTH(TheDate) = 1 AND DATEPART(ISO_WEEK, TheDate) > 51 
      THEN YEAR(TheDate) - 1 
      ELSE YEAR(TheDate) 
    END, 
  DayCount = COUNT(*)
FROM 
  @t
GROUP BY 
  DATEPART(ISO_WEEK, TheDate), 
  CASE 
    WHEN MONTH(TheDate) = 1 AND DATEPART(ISO_WEEK, TheDate) > 51 
    THEN YEAR(TheDate) - 1 
    ELSE YEAR(TheDate) 
  END 
ORDER BY  
  IsoYear,
  IsoWeek

--IsoWeek  IsoYear  DayCount
--     52     2010         6
--      1     2011         2

edited Jul 20 '11 at 21:36

answered Jul 20 '11 at 21:12

Ben Tennen

445
2
6

No good. Some ISO Years have 53 weeks. See [this](http://en.wikipedia.org/wiki/ISO_week_date). Otherwise this looks nice. – Chris Cunningham Jul 20 '11 at 21:21
Corrected. Thanks for spotting the defect Chris. – Ben Tennen Jul 20 '11 at 21:37

t-clausen.dk · Answer 4 · 2011-07-21T08:34:09.307

1

This solution also handles years where the week start the year before.

SELECT DATEPART(ISO_WEEK, col1) WEEK, 
DATEPART(year , col1 - CAST(col1-.5 as int)%7 + 3) YEAR, 
COUNT(*) COUNT 
FROM @t
GROUP BY DATEPART(ISO_WEEK, col1), 
DATEPART(year , col1 - CAST(col1-.5 as int)%7 + 3)
ORDER BY 2,1

edited Jul 21 '11 at 08:34

answered Jul 20 '11 at 21:35

t-clausen.dk

43,517
12
59
92

1

Using `weekday` makes you dependent on `set datefirst` setting. You could probably use `@@datefirst` to fix that. – Mikael Eriksson Jul 20 '11 at 21:43
@Mikael Eriksson I changed my answer so it doesn't use datefirst. Found a better way. I think you will find it interesting – t-clausen.dk Jul 20 '11 at 22:09
Ultimately you seem to be using Thursdays too, only you took a different route to come to that solution. I think this is a clever way of taking advantage of the internal representation of dates in SQL Server. I'd hesitate putting this in production code for my company, but I usually do not mind benefiting from such concise, smart solutions in some little projects of my own. One thing: if `col1` can ever have a chance to be `date` instead of `datetime`, you'll need to add some changes to make it work (`date` is incompatible with `int`). – Andriy M Jul 21 '11 at 05:38
*Andriy I was planning to put this in a function. That is why i was desperately trying to avoid 'set datefirst 1' (which can't be done in functions). I assume that passing a date into a datetime function will work fine. – t-clausen.dk Jul 21 '11 at 09:11

Problem with grouping on european standard week and year

4 Answers4