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
			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
                CASE 
                  WHEN
                    CHARINDEX(cast(DATEPART(year, the_date) as varchar(8000)), @noModificationYears) > 0
                  THEN
                    CONCAT('WK', DATEPART(week, the_date))
                  ELSE
                    CASE
                      WHEN
                        DATEPART(week, the_date) != 1
                      THEN
                        CAST((DATEPART(week, the_date) - 1) as varchar(2))
                      ELSE
                        CASE
                          WHEN
                          CHARINDEX(cast((DATEPART(year, the_date) - 1) as varchar(8000)), @noModificationYears) = 0
                          THEN
                            CAST(DATEPART(week, CONCAT(datepart(year, the_date) - 1, '-12-31')) - 1 as varchar(2))
                          ELSE
                            CAST(DATEPART(week, CONCAT(datepart(year, the_date) - 1, '-12-31')) as varchar(2))
                        END
                    END
                  END
              ELSE
              'M' + CAST((DATEPART(month, the_date)) as varchar(2))+ '.' + CAST(DATEPART(year, the_date) as varchar(4))
            END As PLANBUCKET,
            the_date
		FROM CTE1
		OPTION (MAXRECURSION 0);

Enter Parameters

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