SQLServer - Between two dates, generate table of Year, ISO Week, and Start and End dates-CodePudding

I'm trying to generate a table of Year, ISO Week Num, and Week Beginning/Ending dates, for all weeks between two dates.

This works, except when a Week 53 overflows into a new year. In this case, 2020 Week 53 should span from 2020-12-28 to 2021-01-03. Instead, because I'm grouping by ISO Week and Year, I get one record for 2020 Week 53 2020-12-28 to 2020-12-31, and then another erroneous record for 2021 Week 53 2021-01-01 to 2021-01-03.

DECLARE @windowStart DATE = '20200101',
        @windowEnd   DATE = '20211031';

;WITH report_dates AS (
        SELECT 
           TOP (DATEDIFF(DAY, @windowStart, @windowEnd)   1)  
               report_date = DATEADD(DAY, 
                                     ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,
                                     @windowStart) 
          FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b  
),
report_weeks AS (
    SELECT DATEPART(YEAR,     report_date) AS report_year,
           DATEPART(ISO_WEEK, report_date) AS report_week,
           MIN(report_date)                AS week_beginning_date,
           MAX(report_date)                AS week_ending_date
      FROM report_dates
  GROUP BY DATEPART(YEAR,     report_date), 
           DATEPART(ISO_WEEK, report_date)
)
SELECT * FROM report_weeks ORDER BY week_beginning_date

I've also tried manually calculating the week_ending_date as week_beginning_date 6 - but I still get an erroneous record for 2021 Week 53 beginning in January. I could add one more filter to remove the extra record - maybe using a lag function to detect consecutive Week 53 records and remove the second one - but that seems like it's more complicated solution than necessary. Is there an easier way to do this?

This is in SQLServer

CodePudding user response：

I think this is what your want:

DECLARE @windowStart DATE = '20200101',
        @windowEnd   DATE = '20211031';

with report_dates AS
(SELECT @windowStart AS report_date
UNION ALL
SELECT dateadd(wk, 1, report_date) as NextDate FROM report_dates WHERE report_date < @windowEnd)
SELECT DATEPART(YEAR,     report_date) AS report_year,
       DATEPART(ISO_WEEK, report_date) AS report_week,
       dateadd(dd, -datepart(weekday, report_date) 2, report_date) AS week_beginning_date,
       dateadd(dd, -datepart(weekday, report_date) 8, report_date) AS week_ending_date
FROM report_dates

In the datepart I apply 2 and 8 because my weeks begin on monday. If you have a different week starting day, please take a look at: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-datefirst-transact-sql?view=sql-server-ver15

CodePudding user response：

maybe...?

DECLARE @windowStart date = '20201220',
        @windowEnd date = '20210111';

;
WITH report_dates AS (SELECT TOP (DATEDIFF(DAY, @windowStart, @windowEnd)   1)
                        report_date = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY a.object_id) - 1, @windowStart)
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b)

SELECT
  *,
  DATEPART(ISO_WEEK, report_date) AS report_week,
  CASE
    WHEN DATEPART(ISO_WEEK, report_date) > 50 AND MONTH(report_date) = 1 THEN YEAR(report_date) - 1
    WHEN DATEPART(ISO_WEEK, report_date) = 1 AND  MONTH(report_date) = 12 THEN YEAR(report_date)   1
    ELSE YEAR(report_date)
  END AS report_year
FROM report_dates