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

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

Solution

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.