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?