# Replacing a variable in a loop by records from a Table

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?

• have you considered using a cursor? while it is very similar to a WHILE loop, it allows looping over results from a query... Also, is Date_ID a unique/primary key column, or could there be multiple records with the same value in that field yet different values in the Exit_date field? – Sᴀᴍ Onᴇᴌᴀ Aug 24 '17 at 18:06
• Date_ID is unique/primary – JulGreen Aug 24 '17 at 18:12

### 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.

• That solution still use a loop though, that is what I try to avoid as I expect the date table to be big – JulGreen Aug 24 '17 at 19:38
• Even the first option, which doesn't use the cursor? – Sᴀᴍ Onᴇᴌᴀ Aug 24 '17 at 20:21
• yes the first option works without the loop, thank you – JulGreen Aug 24 '17 at 21:59

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: