如何生成跨年度连续唯一的周一至周日周编号？

需要从[Dates]列获取周编号，要求：

• 以周一为一周起始日
• 每个周具有唯一标识，跨年时不从52重置为1，而是继续计数（如54、55...）

补充说明：此需求是为了在Tableau中对[Week Date Range]列正确排序，否则Tableau会出现如下错误排序：

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

示例代码 #

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

执行结果 #

内容的提问来源于stack exchange，提问作者Yara1994