Unique Week (Monday - Sunday) Number Regardless Of What Year It Is-CodePudding

I would like to get the number of a week from a [Dates] column, starting with Monday as the first day of the week. I would like each week to be uniquely identified. Rather than rolling over at 52 to 1, to continue counting to week 54, 55, etc.

P.S. I need this for correct sort of [Week Date Range] column in Tableau. Otherwise, Tableau sorts grouped data this way:

08/08/2022 - 08/14/2022 
08/09/2021 - 08/15/2021
08/15/2022 - 08/21/2022
08/16/2021 - 08/22/2021

Code:

CREATE TABLE Dates
(Id INT, Dates DATE);

INSERT INTO Dates
VALUES
(1, '2022-12-19'),
(2, '2022-12-22'),
(3, '2022-12-25'),
(4, '2022-12-26'),
(5, '2022-12-29'),
(6, '2022-12-31'),
(7, '2023-01-01'),
(8, '2023-01-06'),
(9, '2023-01-07'),
(10, '2023-01-09')


SELECT *,
       CONCAT(CONVERT(VARCHAR(10), DATEADD(DAY, DATEDIFF(DAY, '19000101', Dates) / 7 * 7, '19000101'), 101), ' - ', CONVERT(VARCHAR(10), DATEADD(DAY, (DATEDIFF(DAY, '19000101', Dates) / 7   1) * 7, '18991231'), 101)) AS [Week Date Range (Monday - Sunday)],       
       DATEPART(ISO_WEEK, Dates) AS [ISO Week Number]

FROM Dates

Id	Dates	Week Date Range (Monday - Sunday)	ISO Week Number
1	2022-12-19	12/19/2022 - 12/25/2022	51
2	2022-12-22	12/19/2022 - 12/25/2022	51
3	2022-12-25	12/19/2022 - 12/25/2022	51
4	2022-12-26	12/26/2022 - 01/01/2023	52
5	2022-12-29	12/26/2022 - 01/01/2023	52
6	2022-12-31	12/26/2022 - 01/01/2023	52
7	2023-01-01	12/26/2022 - 01/01/2023	52
8	2023-01-06	01/02/2023 - 01/08/2023	1
9	2023-01-07	01/02/2023 - 01/08/2023	1
10	2023-01-09	01/09/2023 - 01/15/2023	2

CodePudding user response：

I think you're mostly on the right path. Logic as follows

Find the start of the week
With that start of the week, calculate the relevant week number
Convert that week number into a 'grouping' value that is sorted

You've done the first two steps above; to get the third, I get the 'grouping value' for Tableau to be an int value calculated as 100 * YEAR(date) (week number) - meaning that in your data above, the grouping value will range from 202251 to 202302.

Note that this needs to be done on the 'WeekStart' field, not 'Dates', because 20230101 will return a YEAR of 2023 rather than 2022.

-- Make Monday the first day of week
SET DATEFIRST 1;

-- Calculate 'ISOwkRef' as sort variable
WITH Dates_with_weekstart AS
   (SELECT ID, 
      Dates, 
      DATEADD(day, -1 * (DATEPART(weekday, dates)-1), Dates) AS WeekStart
   FROM dates)
SELECT *,
     100*YEAR(Weekstart)   DATEPART(iso_week, WeekStart) AS ISOwkRef
FROM Dates_with_weekstart
ORDER BY ISOwkRef;

db<>fiddle with answer above here.

Results

ID  Dates       WeekStart   ISOwkRef
1   2022-12-19  2022-12-19  202251
2   2022-12-22  2022-12-19  202251
3   2022-12-25  2022-12-19  202251
4   2022-12-26  2022-12-26  202252
5   2022-12-29  2022-12-26  202252
6   2022-12-31  2022-12-26  202252
7   2023-01-01  2022-12-26  202252
8   2023-01-06  2023-01-02  202301
9   2023-01-07  2023-01-02  202301
10  2023-01-09  2023-01-09  202302

Edit: Alternatively, if you prefer to have the weeks numbered from the first week and then increasingly, you can do it similar to above, but

First calculate the week number for the earliest week
Calculate the difference in weeks from that earliest week, and add it to the number

I think it's a bit more convoluted, but here is an approach to do it:

WITH Dates_with_weekstart AS
   (SELECT ID, 
      Dates, 
      DATEADD(day, -1 * (DATEPART(weekday, dates)-1), Dates) AS WeekStart
   FROM dates)
SELECT Dates_with_weekstart.ID, 
       Dates_with_weekstart.Dates, 
       Dates_with_weekstart.Weekstart,
       DATEPART(ISO_week, First_Weekstart)   DATEDIFF(week, First_Weekstart, Dates_with_weekstart.Weekstart) AS ISOWkNum_rolling
  FROM Dates_with_weekstart
       CROSS JOIN    
         (SELECT MIN(WeekStart) AS First_Weekstart
         FROM Dates_with_weekstart
         ) AS FirstWkStart
  ORDER BY ISOWkNum_rolling;

Results:

ID  Dates       Weekstart   ISOWkNum_rolling
1   2022-12-19  2022-12-19  51
2   2022-12-22  2022-12-19  51
3   2022-12-25  2022-12-19  51
4   2022-12-26  2022-12-26  52
5   2022-12-29  2022-12-26  52
6   2022-12-31  2022-12-26  52
7   2023-01-01  2022-12-26  52
8   2023-01-06  2023-01-02  53
9   2023-01-07  2023-01-02  53
10  2023-01-09  2023-01-09  54

Fiddle above has been adjusted with this second approach.

Feel free to add the varchar 'Date range' field you have; this answer is just working with the key data.

CodePudding user response：

Your problem is not really that you are in need of a running week number. Your problem is that the "Week Date Range" that you have asked Tableau to sort is a string formatted in part as MM/DD/YYYY, which will never sort correctly across years.

The simplest solution is to provide either a true date value such as the calculated week start date or that same date as a string formatted as "yyyy-mm-dd" using CONVERT(VARCHAR(10), WeekStart, 120).

However, if you really want a unique week number, you can use the DATEDIFF(day, ...) / 7 function relative to a "reference Monday" to give you a continuous integer week number. (The "reference Monday" must precede all dates in the data to avoid negative diffs.)

The following includes both:

SET DATEFIRST 1
DECLARE @ReferenceMonday DATE = '1900-01-01' -- This just happens to be a Monday

SELECT
    *,
    DATENAME(weekday, DATES) AS Weekday,
    CONCAT(CONVERT(VARCHAR(10), DATEADD(DAY, DATEDIFF(DAY, '19000101', Dates) / 7 * 7, '19000101'), 101), ' - ', CONVERT(VARCHAR(10), DATEADD(DAY, (DATEDIFF(DAY, '19000101', Dates) / 7   1) * 7, '18991231'), 101)) AS [Week Date Range (Monday - Sunday)],       
    DATEDIFF(day, @ReferenceMonday, Dates) / 7 AS SortableWeek,
    CONVERT(VARCHAR(10), DATEADD(DAY, DATEDIFF(DAY, '19000101', Dates) / 7 * 7,'19000101'), 120) AS [SortableWeekStart]
FROM Dates
ORDER BY Id

(I have also added weekday name to the select list as a reference.)

The week start and finish date calculations can also be simplified by combining DATEPART(weekday,...) with DATEADD(day).

    DATEADD(day, 1-DATEPART(weekday, D.Dates), D.Dates) AS WeekStart
    DATEADD(day, 7-DATEPART(weekday, D.Dates), D.Dates) AS WeekFinish

A technique that may improve readability and reduce duplication of expressions is to use CROSS APPLY to separate calculations from the final select list.

Combining the above could result in a query rewritten as follows:

SET DATEFIRST 1
DECLARE @ReferenceMonday DATE = '1900-01-01' -- This just happens to be a Monday

SELECT
    D.*,
    DATENAME(weekday, DATES) AS Weekday,
    DS.[Week Date Range (Monday - Sunday)],       
    WK.SortableWeek,
    DS.SortableWeekStart
FROM Dates D
CROSS APPLY (
    SELECT
        DATEADD(day, 1-DATEPART(weekday, D.Dates), D.Dates) AS WeekStart,
        DATEADD(day, 7-DATEPART(weekday, D.Dates), D.Dates) AS WeekFinish,
        DATEDIFF(day, @ReferenceMonday, Dates) / 7 AS SortableWeek
) WK
CROSS APPLY (
    SELECT
        CONCAT(CONVERT(VARCHAR(10), WK.WeekStart, 101), ' - ', CONVERT(VARCHAR(10), WK.WeekFinish, 101)) AS [Week Date Range (Monday - Sunday)],
        CONVERT(VARCHAR(10), WeekStart, 120) AS SortableWeekStart
) DS
ORDER BY Id

Both of the above queries yield the following results:

Id	Dates	Weekday	Week Date Range (Monday - Sunday)	SortableWeek	SortableWeekStart
1	2022-12-19	Monday	12/19/2022 - 12/25/2022	6416	2022-12-19
2	2022-12-22	Thursday	12/19/2022 - 12/25/2022	6416	2022-12-19
3	2022-12-25	Sunday	12/19/2022 - 12/25/2022	6416	2022-12-19
4	2022-12-26	Monday	12/26/2022 - 01/01/2023	6417	2022-12-26
5	2022-12-29	Thursday	12/26/2022 - 01/01/2023	6417	2022-12-26
6	2022-12-31	Saturday	12/26/2022 - 01/01/2023	6417	2022-12-26
7	2023-01-01	Sunday	12/26/2022 - 01/01/2023	6417	2022-12-26
8	2023-01-06	Friday	01/02/2023 - 01/08/2023	6418	2023-01-02
9	2023-01-07	Saturday	01/02/2023 - 01/08/2023	6418	2023-01-02
10	2023-01-09	Monday	01/09/2023 - 01/15/2023	6419	2023-01-09

See this db<>fiddle for a working example.