Difficulties comprehending ISO_week and week in Microsoft SQL, what's happening exactly?

Question

I have a set of data from 8 months or so, which is partly 2012 and partly 2013 data. I'm not sure if this is a cultural thing, but I'm Dutch and I believe the data in the database should be interpreted by this standard.

So I've written two queries, one with the regular week expression and one with the ISO_week, this query looks like this:

SELECT datepart(ISO_WEEK,date_dtm) as date_detail,
datepart(yyyy,date_dtm) as date_year, 
(sum(sum_value)/sum(user_count)) as value 

FROM kpi_record 
WHERE kpi_series_id = '15'
AND date_dtm > dateadd(ww,-12,'2013-02-28 00:00:00.000')
group by datepart(ISO_WEEK,date_dtm), datepart(yyyy,date_dtm)
ORDER BY datepart(yyyy,date_dtm), datepart(ISO_WEEK,date_dtm)

--

SELECT datepart(ww,date_dtm) as ww,
datepart(yyyy,date_dtm) as date_year, 
(sum(sum_value)/sum(user_count)) as value 

FROM kpi_record 
WHERE kpi_series_id = '15'
AND date_dtm > dateadd(ww,-12,'2013-02-28 00:00:00.000')
group by datepart(yyyy,date_dtm), datepart(ww,date_dtm)
ORDER BY datepart(yyyy,date_dtm), datepart(ww,date_dtm)

The results of these two queries are respectively:

1   2012    7,14
49  2012    7,31475409836066
50  2012    7,39261285909713
51  2012    7,47905477980666
52  2012    7,30618401206636
1   2013    7,49925705794948
2   2013    7,26598837209302
3   2013    7,24533333333333
4   2013    7,22245989304813
5   2013    6,96774193548387
6   2013    7,24523160762943
7   2013    7,14718019257221
8   2013    7,34691195795007
9   2013    7,23430962343096

and

49  2012    7,4537037037037
50  2012    7,33109017496635
51  2012    7,4656652360515
52  2012    7,36874051593323
53  2012    7,13888888888889
1   2013    7,50515463917526
2   2013    7,33190271816881
3   2013    7,17693315858453
4   2013    7,24209378407852
5   2013    7,0201072386059
6   2013    7,19281914893617
7   2013    7,17278911564626
8   2013    7,3283378746594
9   2013    7,24733096085409

My questions, in this order, are:

• In the second query (with regular ww), what does this week 53 hold worth of data? It was my understanding that this is a couple of days that entered 2013, yet still belonged to week 52, but due to the year being finished, it adds an extra week for those days. Is this correct? If not, please enlighten me.
• In the first query (iso_week), what is the week 1? I believe it's the week 53 as seen in the second query (regular ww). But it should be week 1 of 2013 then, or maybe added to the first week of 2013? I have trouble understanding what's going on here.
• Even if this is explained, I don't quite understand why 14 rows of data are being returned. With my query, I want to go back in time 12 weeks from the date I provided (hardcoded in example). How can I possibly get 12 rows of legit data of each week, and not 13 or 14? If I use day instead of week, it goes back 12 days and thus I have 12 rows returned to me.

Thanks for your help, I hope I'm making sense.

Answer 1

ISO Standards define the first week of the year (Where week is Monday to Sunday) where 4 or more days fall into that week (i.e. the majority of the week is in that year), that can be simplified to the first week of the year containing a Thursday.

DATEPART(WEEK, is much more simplistic and is just a count of the number of week boundaries (defined by DATEFIRST) passed since the start of the year, always commencing at 1, so you can get three different week numbers for three consecutive days as follows:

SET DATEFIRST 3;
SELECT  [20121231] = DATEPART(WEEK, '20121231'),
        [20130101] = DATEPART(WEEK, '20130101'),
        [20130102] = DATEPART(WEEK, '20130102');

There are 2 contributory factors to the reason you are getting 14 rows when trying to analyse 12 weeks of data, the reason applies to both ISO_WEEK, and WEEK, your date range is 20121206 to 20130228, even though these dates are 12 weeks apart if your DATEFIRST does not match the weekday of the start and end dates then your date range will span 13 weeks.

The 14th row in the ISO_WEEK function comes because there is no ISO_YEAR function, in ISO standards week 1 of 2013 started on 31st December 2012, however because DATEPART(YEAR gives 2012 for this it splits week 1 into 2 rows:

Year    Week    DaysInRow
2012    1       1
2013    1       6

The 14th Week when using WEEK comes because of the simple calculation method, the same week (31st December 2012 - 6th Jan 2013) is also split into 2, but as follows

Year    Week    DaysInRow
2012    53      1
2013    1       6

If you have a calendar table then you should have ISO_YEAR and ISO_WEEK columns on this so (if you don't add them) you easily see that 20121231 is week 1 of 2013 for reporting purposes, if you do not have a calendar table (I'd suggest you create one) you can create your own UDF:

CREATE FUNCTION dbo.ISO_YEAR @Date DATETIME
RETURNS INT
AS
BEGIN
    DECLARE @ISOyear INT = DATEPART(YEAR, @Date);

    -- Special cases: Jan 1-3 may belong to the previous year
    IF (DATEPART(MONTH, @DATE) = 1 AND DATEPART(ISO_WEEK, @DATE) > 50)
        SET @ISOyear = @ISOyear - 1;

    -- Special case: Dec 29-31 may belong to the next year
    IF (DATEPART(MONTH, @DATE) = 12 AND DATEPART(ISO_WEEK, @DATE) < 45)
        SET @ISOyear = @ISOyear + 1;

    RETURN @ISOYear;
END

Answer 2

I use the simpler code below to give me a reliable ISO Year:

DATEPART(YY, @TheDate - (DATEPART(DW, @TheDate) - 4))

… and I wrote the UDF below — which includes the snippet above — to give me a reliable ISO year-week character string I can use for reporting or sorting-by-week-number.

The UDF enforces a two-character week string, to avoid the ‘gotcha’ of (for example) 2016-9 sorting after 2016-10 (I bet you’ve already been there and done that; I certainly have!):

CREATE FUNCTION [dbo].[ISOYearAndWeek] 
(@TheDate DATETIME) RETURNS NCHAR(7) AS
BEGIN

  DECLARE @Result NCHAR(7)

  SELECT @Result = 
    CONVERT(CHAR(4), DATEPART(YY, @TheDate - (DATEPART(DW, @TheDate) - 4)))
    + '-'
    + CONVERT(CHAR(2), RIGHT('00' + CONVERT(VARCHAR(2), DATEPART(ISOWK, @TheDate)), 2)) 

    RETURN @Result

END