I am trying to create a SQL statement that will identify my Start Date and End Date by identifying my Interval variable.
I have the below statement, which works for the first two Interval options, but if I choose any of the following Interval options, it defaults to the @Start_Date and @End_Date variables.
DECLARE @Interval NVARCHAR(20) , @StartDate DateTime , @EndDate DateTime , @Start_Date DateTime , @End_Date DateTime SET @Interval = '3 Months (Last 3 full Months)' SET @Start_Date = '2015-07-01' SET @End_Date = '2015-07-31' SELECT @StartDate = CASE @Interval WHEN 'Daily (Yesterday)' THEN DATEADD(dd, - 1, DATEDIFF(dd, 0, GETDATE())) WHEN 'Weekly (Sun-Sat)' THEN DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), - 1) WHEN 'Monthly (Last full Month)' THEN DATEADD(mm, - 1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) WHEN '3 Months (Last 3 full Months)' THEN DATEADD(mm, - 3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) WHEN 'Yearly (Last full Year)' THEN DATEADD(yy, - 1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) WHEN 'Yearly (Last 12 full Months)' THEN DATEADD(mm, - 12, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) ELSE @Start_Date END, @EndDate = CASE @Interval WHEN 'Daily (Yesterday)' THEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) WHEN 'Weekly (Sun-Sat)' THEN DATEADD(ms, - 3, DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 6)) WHEN 'Monthly (Last full Month)' THEN DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))) WHEN '3 Months (Last 3 full Months)' THEN DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))) WHEN 'Yearly (Last full Year)' THEN DATEADD(ms, - 3, DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))) WHEN 'Yearly (Last 12 full Months)' THEN DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))) ELSE DateAdd(dd, 1, @End_Date) END SELECT @StartDate, @EndDate