whatwhatwhat whatwhatwhat - 5 months ago 19
SQL Question

SQL: Is there a way to iteratively DECLARE a variable?

Is there a way to accomplish something like this in SQL:

DECLARE @iter = 1

WHILE @iter<11
BEGIN
DECLARE @('newdate'+@iter) DATE = [some expression that generates a value]
SET @iter = @iter + 1
END


At the end I would have 10 variables:

@newdate1
@newdate2
@newdate3
@newdate4
@newdate5
@newdate6
@newdate7
@newdate8
@newdate9
@newdate10


Update:

Based on a comment, I think I should specify why I want to do this. I am working with Report Builder 3.0. I am going to make a report where the input will be a
start date
and an
end date
(in addition to one other parameter). This will generate data between the date range. However, the user also wants to check the same date range for all other years in the set 2013 -> current year.

The tricky part is this: the user can enter a date range in any year between 2013 and the current year and I need to return data for the input year and also data for the other years. For example, if the user enters in 1/1/2014 - 6/1/2014 then I need to return the same range but for the years 2013, 2015, and 2016.

Example input:

1/1/2016 - 6/1/2016


Report must generate data for these values:

1/1/2013 - 6/1/2013
1/1/2014 - 6/1/2014
1/1/2015 - 6/1/2015
1/1/2016 - 6/1/2016


If there is a better way to do this, I'm all ears.

Answer

I use a UDF to create Dynamic Date Ranges.

For exanple

Select DateR1=RetVal,DateR2=DateAdd(MM,5,RetVal) from [dbo].[udf-Create-Range-Date]('2013-01-01','2016-01-01','YY',1) 

Returns

DateR1      DateR2
2013-01-01  2013-06-01
2014-01-01  2014-06-01
2015-01-01  2015-06-01
2016-01-01  2016-06-01

The UDF

CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)

Returns 
@ReturnVal Table (RetVal datetime)

As
Begin
    With DateTable As (
        Select DateFrom = @DateFrom
        Union All
        Select Case @DatePart
               When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
               When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
               When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
               When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
               When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
               When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
               When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
               When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
               End
        From DateTable DF
        Where DF.DateFrom < @DateTo
    )

    Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)

    Return
End

-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','YY',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','DD',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-31','MI',15) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-02','SS',1) 

Stripped Down version - NON UDF This can be injected into your SQL

Declare @startdate Date ='1/1/2014'   -- user supplied value
Declare @enddate Date = '6/1/2014'    -- user supplied value

Declare @DateFrom Date = cast('2013-'+cast(month(@StartDate) as varchar(10))+'-'+cast(Day(@StartDate) as varchar(10)) as date)
Declare @DateTo Date   = cast(cast(Year(GetDate()) as varchar(10))+'-'+cast(month(@enddate) as varchar(10))+'-'+cast(Day(@enddate) as varchar(10)) as date) 
Declare @Incr int = DateDiff(MM,@startdate,@enddate)  -- made to be dynamic based on the user supplied dates

Declare @DateRange Table (DateR1 date,DateR2 Date)

;with DateTable As (
    Select DateFrom = @DateFrom
    Union All
    Select DateAdd(YY, 1, df.dateFrom)
    From DateTable DF
    Where DF.DateFrom < @DateTo
)
Insert into @DateRange(DateR1,DateR2) Select DateR1=DateFrom,DateR2=DateAdd(MM,@Incr,DateFrom) From DateTable option (maxrecursion 32767)

Select * from @DateRange
Comments