Q&A for people looking to improve their interpersonal communication skills
SET DATEFIRST 1; WITH mycte AS ( SELECT DATEADD(dd, 0, '2018-12-31') DateValue UNION ALL SELECT DateValue + 1 FROM mycte WHERE DateValue + 1 <= '2021-12-31' ) SELECT datepart(year, DateValue) as DateYear, datepart(year, dateadd(dd, -datepart(dw, DateValue) + 1, DateValue)) as WeekYear, datepart(month, DateValue) as TheMonth, CASE WHEN datepart(week, DateValue) != 1 AND datepart(week, dateadd(dd, - datepart(dw, CONCAT(datepart(year, DateValue), '-01-01')) + 1, CONCAT(datepart(year, DateValue), '-01-01'))) in (52,53) AND CONCAT(datepart(year, DateValue), '-01-01') > '2018-12-31' and CONCAT(datepart(year, DateValue), '-01-01') < '2021-12-31' AND (datepart(week, dateadd(dd, - datepart(dw, dateadd(dd, - datepart(dw, DateValue), DateValue)) + 1, dateadd(dd, - datepart(dw, DateValue), DateValue))) - datepart(week, DateValue)) != 1 THEN datepart(week, DateValue) - 1 WHEN datepart(week, DateValue) = 1 AND datepart(week, dateadd(dd, -1, '2019-12-31')) in (53) THEN datepart(week, dateadd(dd, - datepart(dw, DateValue) + 1, DateValue)) - 1 ELSE datepart(week, dateadd(dd, - datepart(dw, DateValue) + 1, DateValue)) END as TheWeek ,DateValue as TheDate FROM mycte OPTION (MAXRECURSION 0);