SQLServer -在两个日期之间，生成表的年份、ISO周以及开始和结束日期

DECLARE @StartDate DATE = '2022-01-01';
DECLARE @EndDate DATE = '2022-12-31';

WITH DateRanges AS (
    SELECT
        YEAR(dateValue) AS Year,
        DATEPART(ISO_WEEK, dateValue) AS ISOWeek,
        MIN(dateValue) AS StartDate,
        MAX(dateValue) AS EndDate
    FROM (
        SELECT DATEADD(DAY, number, @StartDate) AS dateValue
        FROM master..spt_values
        WHERE type = 'P'
            AND DATEADD(DAY, number, @StartDate) <= @EndDate
    ) AS Dates
    GROUP BY YEAR(dateValue), DATEPART(ISO_WEEK, dateValue)
)
SELECT *
FROM DateRanges
ORDER BY Year, ISOWeek;