Help with some code

[G12Consult]

Hi,

I have the following code which will give me a list of every fortnight between two dates however I want to be able to make the type part of datadd (so WEEK, MONTH, YEAR) etc dynamic. Is this possible? I have tried creating a user defined variable for it but it didn't work.

Basically if the rental period is weekly, this datepart would be WEEK, if its monthly it would be MONTH etc

DECLARE @OFFSET int
SET @OFFSET = 2

DECLARE @PERIOD VARCHAR


declare @startdate date
set @startdate = '2020-08-13'

declare @enddate date
set @enddate = '2021-12-31'

declare @rentalperiod int
set @rentalperiod = 2


--RENTAL_PERIOD_ID
--1	Weekly
--2	Fortnightly
--3	4 Weekly
--4	Monthly
--5	Yearly
--6	Once Off
SET @PERIOD =
CASE @rentalperiod  
	WHEN 1 THEN 'WEEK'
	WHEN 2 THEN 'WEEK'
	WHEN 3 THEN 'WEEK'
	WHEN 4 THEN 'MONTH'
	WHEN 5 THEN 'YEAR'
	
END

SET @OFFSET =
 
CASE @rentalperiod  
	WHEN 1 THEN 1
	WHEN 2 THEN 2
	WHEN 3 THEN 4
	WHEN 4 THEN 1
	WHEN 5 THEN 1
	
END


;WITH WeeklyCTE AS 

(

	

    SELECT  MIN(@startdate) AS weekstart_date
    FROM    COMMON_DATA.DATE_TABLE
    UNION ALL
	    SELECT  DATEADD(WEEK, @OFFSET, weekstart_date)
    FROM    WeeklyCTE
    WHERE   DATEADD(WEEK, @OFFSET, weekstart_date) >= @startdate AND DATEADD(WEEK, @OFFSET, weekstart_date) <=@enddate

	
	)
SELECT  w.weekstart_date
FROM    WeeklyCTE w
        LEFT JOIN COMMON_DATA.DATE_TABLE t ON w.weekstart_date = t.calendar_date

		option (maxrecursion 0)

 

[Andrzejek]

Would that help: Passing DatePart variable to DateAdd()

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SaltyTheFrog]

Maybe dynamic SQL

https://stackoverflow.com/questions/34622295/defining-type-as-a-variable-dateaddtype-value-date-sql