0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for enthusiasts and power users of the Android operating system

SET DATEFIRST 1;
DECLARE @sDate date
DECLARE @eDate date;
DECLARE @noModificationYears nvarchar(max);
DECLARE @Count int;
SET @sDate = '2020-01-01';
SET @eDate = '2020-12-31';
SET @noModificationYears = '2018,2024,2029,2035';
SET @Count = 1;
with CTE1 As  
(  
    Select DATEADD(day, 0, @sDate) as TheDate, 'D' as PERIND
    UNION ALL  
    Select DATEADD(day, 1, TheDate) as TheDate, 'D' as PERIND from CTE1  
    where TheDate < @eDate 
),
CTE2 As
(
  Select c1.TheDate, 'D' as PERIND from CTE1 c1
  UNION ALL
  Select DATEADD(day, 0, c2.TheDate) as TheDate, 'W' as PERIND from CTE1 c2
)

SELECT 
  '001' AS MODELID,
  TheDate AS PLANDATE,
  PERIND,
  CASE
    WHEN
      PERIND = 'W'
    THEN
      CONCAT(
            'WK',
            RIGHT('0' + CAST(
                    CASE
                        WHEN CHARINDEX(
                            cast(DATEPART(year, TheDate) as varchar(8000)),
                            @noModificationYears
                        ) > 0 THEN DATEPART(week, TheDate)
                        ELSE CASE
                            WHEN DATEPART(week, TheDate) != 1 THEN DATEPART(week, TheDate) - 1
                            ELSE CASE
                                WHEN CHARINDEX(
                                    cast((DATEPART(year, TheDate) - 1) as varchar(8000)),
                                    @noModificationYears
                                ) = 0 THEN DATEPART(
                                    week,
                                    CONCAT(datepart(year, TheDate) - 1, '-12-31')
                                ) - 1
                                ELSE DATEPART(
                                    week,
                                    CONCAT(datepart(year, TheDate) - 1, '-12-31')
                                )
                            END
                        END
                    END
                 AS VARCHAR(2)
            ),
            2
        ),
        '.',
        DATEPART(
            year,
            dateadd(dd, - datepart(dw, TheDate) + 1, TheDate)
        )
    )
    ELSE
      CAST(TheDate as varchar(10))
    END AS PLANBUCKET

FROM CTE2 option (maxrecursion 0);

fork query

edited aug 6

created aug 1 anonymous