Replacing a variable in a loop by records from a Table

Question

I have a Date Table like this one:

Exit_Date  Date_ID
2017-05-31    1
2017-04-26    2
2017-01-02    3
2016-12-24    4
2016-11-27    5

I use a loop to insert each of those Dates into a CTE to generates the last 15 years of these dates like this:

declare @DI int = 1
declare @d date
while @DI <=5
begin
    select @d = Exit_Date from Date_Table where Date_ID = @DI
    declare @EDTable table (Exit_Date  Date);
    with
      a as(
        select dateadd(yy,-1,@d) d,0 i
          union all
        select dateadd(yy,-1,d),i+1 from a where i<14
      ),
      b as(select d,datediff(dd,0,d)%7 dd from a)
     insert into @EDTable select d from b;
      set @DI = @DI + 1
 end

The results is correct, I get 75 rows with my dates. I would like to know if there is a way to get rid of the WHILE loop by replacing variable @d by each date record from Date_Table?

Answer 1

Selecting from Date_Table in a

One option is to select Exit_Date direct from Date_Table in a. The ordering of the end results won't be the same so you may need to add an ORDER BY clause (e.g. with DATEPART()).

 with
      a as(
        select dateadd(yy,-1,Exit_date) d,0 i
        from Date_Table WHERE Date_ID <=5
          union all
        select dateadd(yy,-1,d),i+1 from a where i<14
      ),
      b as(select d,datediff(dd,0,d)%7 dd from a)
SELECT d from b --order by DATEPART(MM,d) desc

See a sample here in this SQLFidle.

Cursor

Like I mentioned in my comment, another option is to use a Transact-SQL cursor. In the squery below, we keep the temp variables @d and @EDTable. The SELECT statement from Date_Table could have ORDER BY Date_ID ASC added if you wanted to ensure those go in order (in case the records were not added sequentially).

declare @d date;
declare @EDTable table (Exit_Date  Date);
declare dateCursor CURSOR FOR 
    select Exit_Date from Date_Table WHERE Date_Id <=5;
    
OPEN dateCursor  

FETCH NEXT FROM dateCursor INTO @d

WHILE @@FETCH_STATUS = 0  
BEGIN 
    with
      a as(
        select dateadd(yy,-1,@d) d,0 i
          union all
        select dateadd(yy,-1,d),i+1 from a where i<14
      ),
      b as(select d,datediff(dd,0,d)%7 dd from a)
     insert into @EDTable select d from b;--*/
    FETCH NEXT FROM dateCursor   
    INTO @d 
END   
CLOSE dateCursor;  
DEALLOCATE dateCursor;  

SELECT * FROM @EDTable

I am trying to get a SQL Fiddle working but having issues with the line endings. I will update when I figure that out.

Answer 2

Another option that will perform better at larger scales than recursion would be a numbers table, or virtual numbers table.

Using row_number() and the system view master..spt_values for a source of rows:

select Exit_Date = dateadd(year, -x.n, d.exit_date)
from date_table d
  cross join (
    select top (15) --<-- number of rows is 15 for 0 to 14
      n = row_number() over(order by (select 1))-1
    from master..spt_values
    order by n
  ) x
where d.date_id <= 5

dbfiddle.uk demo

Reference:

• Generate a set or sequence without loops - 1 - Aaron Bertrand
• Generate a set or sequence without loops - 2 - Aaron Bertrand
• Generate a set or sequence without loops - 3 - Aaron Bertrand
• The "Numbers" or "Tally" Table: What it is and how it replaces a loop - Jeff Moden