SET DATEFIRST 1; WITH mycte AS ( SELECT DATEADD(dd, 0, '...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Interpersonal Skills

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);

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results