0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Tridion developers and administrators

SET DATEFIRST 1;
DECLARE @PERIND int = 2;

WITH mycte AS
(
 SELECT DATEADD(dd, 0, '2019-01-01') DateValue
 UNION ALL
 SELECT  DateValue + 1
 FROM    mycte   
 WHERE   DateValue + 1 < '2020-12-31'
)

SELECT distinct
  datepart(year, dateadd(dd, -datepart(dw, DateValue) + 1, DateValue)) as TheYear,
  --(datepart(week, dateadd(dd, - datepart(dw, dateadd(dd, - datepart(dw, DateValue), DateValue)) + 1, dateadd(dd, - datepart(dw, DateValue), DateValue))) - datepart(week, DateValue)) as diff,
  CASE
    WHEN 
      @PERIND = 1
    THEN
      datepart(month, DateValue)
    WHEN
      @PERIND = 2
    THEN
      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') >= '2020-01-01' and CONCAT(datepart(year, DateValue), '-01-01') < '2020-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
            ELSE
              datepart(week, dateadd(dd, - datepart(dw, DateValue) + 1, DateValue))
      END
   END as PlanBuk,
   DateValue as TheDate
FROM    mycte
OPTION (MAXRECURSION 0);

fork query

edited aug 4

created aug 1 anonymous