Problem with grouping on european standard week and year

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

52 

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开发者_StackOverflow中文版 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

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

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]

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

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