2
\$\begingroup\$

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?

\$\endgroup\$
  • \$\begingroup\$ 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? \$\endgroup\$ – Sᴀᴍ Onᴇᴌᴀ Aug 24 '17 at 18:06
  • \$\begingroup\$ Date_ID is unique/primary \$\endgroup\$ – JulGreen Aug 24 '17 at 18:12
1
\$\begingroup\$

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.

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

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:

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.