DECLARE @no_modification_years nvarchar(max); DECLARE @f...

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

DECLARE @no_modification_years nvarchar(max);

DECLARE @from_date date = '2020-01-01';

DECLARE @to_date date = '2020-12-31';

DECLARE @days int = 26;

DECLARE @days_from_date date;

DECLARE @days_to_date date;

DECLARE @weeks int = 280;

DECLARE @weeks_from_date date;

DECLARE @weeks_to_date date;

DECLARE @months int = 60;

DECLARE @months_from_date date;

DECLARE @months_to_date date;

DECLARE @noModificationYears nvarchar(max);

SET
    @days_from_date = DATEADD(dd, 0, @from_date)
SET
    @days_to_date = DATEADD(dd, @days - 1, @from_date)
SET
    @weeks_from_date = DATEADD(dd, 1, @days_to_date)
SET
    @weeks_to_date = DATEADD(dd, @weeks, @days_to_date)
SET
    @months_from_date = DATEADD(dd, 1, @weeks_to_date)
SET
    @months_to_date = DATEADD(dd, @months, @weeks_to_date)
SET
    @noModificationYears = '2018,2024,2029,2035';

;with CTE1 As (
    SELECT
        DATEADD(day, 0, @from_date) as the_date
    UNION
    ALL
    SELECT
        DATEADD(day, 1, the_date) AS the_date
    from
        CTE1
    WHERE
        the_date < @to_date
)
SELECT
    '001' AS MODELID,
    the_date AS PLANDATE,
    CASE
        WHEN the_date >= @days_from_date
        AND the_date <= @days_to_date THEN 'D'
        WHEN the_date >= @weeks_from_date
        AND the_date <= @weeks_to_date THEN 'W'
        ELSE 'M'
    END AS PERIND,
    CASE
        WHEN the_date >= @days_from_date
        AND the_date <= @days_to_date THEN CAST(the_date as varchar(8000))
        WHEN the_date >= @weeks_from_date
        AND the_date <= @weeks_to_date THEN CONCAT(
            'WK',
            RIGHT('0' + CAST(
                    CASE
                        WHEN CHARINDEX(
                            cast(DATEPART(year, the_date) as varchar(8000)),
                            @noModificationYears
                        ) > 0 THEN DATEPART(week, the_date)
                        ELSE CASE
                            WHEN DATEPART(week, the_date) != 1 THEN DATEPART(week, the_date) - 1
                            ELSE CASE
                                WHEN CHARINDEX(
                                    cast((DATEPART(year, the_date) - 1) as varchar(8000)),
                                    @noModificationYears
                                ) = 0 THEN DATEPART(
                                    week,
                                    CONCAT(datepart(year, the_date) - 1, '-12-31')
                                ) - 1
                                ELSE DATEPART(
                                    week,
                                    CONCAT(datepart(year, the_date) - 1, '-12-31')
                                )
                            END
                        END
                    END
                 AS VARCHAR(2)
            ),
            2
        ),
        '.',
        DATEPART(
            year,
            dateadd(dd, - datepart(dw, the_date) + 1, the_date)
        )
    )
    ELSE CONCAT(
        'M',
        RIGHT('0' + CAST(DATEPART(month, the_date) AS varchar(2)), 2),
        '.',
        DATEPART(year, the_date)
    )
END As PLANBUCKET
FROM
    CTE1 OPTION (MAXRECURSION 0);

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results