How to calculate recursive datetime in sql?

Question

i want to calculate an expiry date using open_dt column from a table, for example if the open_dt is '2010-08-02 00:00:00.000' the expiry date should be '2014-08-02 00:00:00.000'.

so with open_dt i should keep adding 2 to the year and the expiry date should be less than the getdate().

i tried using CTE but it just calculates for only one loop. for example for open_dt '2010-08-02 00:00:00.000' it shows the result as '2012-08-02 00:00:00.000'

WITH Test (CUSTOMER,OPENED,EXPIRED)
as
(

select  A.CUSTOMER_NO,A.OPENED_DT, DATEADD(YEAR,2, max(A.OPENED_DT)) as EXPIRED
  from ACCOUNTS_MASTER A GROUP BY  A.CUSTOMER_NO , A.OPENED_DT
  union all 
  select  CUSTOMER,OPENED, DATEADD(YEAR,2, EXPIRED)
  from Test T  

  )
  select  CUSTOMER,OPENED, EXPIRED
  from Test T

I couldn't figure out Please help

Answer 1

I would not use a recursive CTE here, don't be sucked in by their syntactic sugar, they are little better than a while loop or a cursor, which people are a lot faster to condemn. There are very few scenarios (if any) that do not involve hierarchical data where a recursive CTE is going to be the right solution.

Here, you probably only need a small set of numbers, assuming you won't have more than 100 years worth of data you need 50 rows, which you can get using a couple of simple table valued constructors and cross joining them, finally ROW_NUMBER() will give you your sequence:

SELECT ROW_NUMBER() OVER(ORDER BY N1.N)
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n1 (N)
    CROSS JOIN (VALUES(1),(1),(1),(1),(1)) AS n2 (N);

Now you can join your set of numbers to your table to get your 2 year intervals for the expiry date:

WITH Numbers (Number) AS
(   SELECT ROW_NUMBER() OVER(ORDER BY N1.N) - 1
    FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n1 (N)
        CROSS JOIN (VALUES(1),(1),(1),(1),(1)) AS n2 (N)
)
SELECT  am.Customer_No, 
        am.Opened_Dt, 
        Expired = DATEADD(YEAR, n.Number * 2, Opened_Dt)
FROM    Accounts_Master AS am
        CROSS JOIN Numbers AS n;

This will of course give you 50 rows for each record, spanning 100 years after Opened_Dt, which is not required, so you need to filter the results to where the next expiry date is in the future, and the current one is in the past:

WITH Numbers (Number) AS
(   SELECT ROW_NUMBER() OVER(ORDER BY N1.N) - 1
    FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n1 (N)
        CROSS JOIN (VALUES(1),(1),(1),(1),(1)) AS n2 (N)
)
SELECT  am.Customer_No, 
        am.Opened_Dt, 
        Expired = DATEADD(YEAR, n.Number * 2, Opened_Dt)
FROM    Accounts_Master AS am
        CROSS JOIN Numbers AS n
WHERE   DATEADD(YEAR, n.Number * 2, Opened_Dt) <= GETDATE()
AND     DATEADD(YEAR, (n.Number * 2) + 2, Opened_Dt) > GETDATE()

---

FULL WORKING EXAMPLE

WITH Numbers (Number) AS
(   SELECT ROW_NUMBER() OVER(ORDER BY N1.N) - 1
    FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n1 (N)
        CROSS JOIN (VALUES(1),(1),(1),(1),(1)) AS n2 (N)
)
SELECT  am.Customer_No, 
        am.Opened_Dt, 
        Expired = DATEADD(YEAR, n.Number * 2, Opened_Dt)
FROM    (VALUES
            (1, CAST('2010-08-02' AS DATE)),
            (2, CAST('2013-04-27' AS DATE)),
            (3, CAST('2013-08-24' AS DATE)),
            (4, CAST('2015-03-19' AS DATE))
        ) AS am (Customer_no, Opened_dt)
        CROSS JOIN Numbers AS n
WHERE   DATEADD(YEAR, n.Number * 2, Opened_Dt) <= GETDATE()
AND     DATEADD(YEAR, (n.Number * 2) + 2, Opened_Dt) > GETDATE()
ORDER BY Customer_No;

Example results:

Customer_No Opened_Dt       Expired
--------------------------------------
1           2010-08-02      2014-08-02
2           2013-04-27      2015-04-27
3           2013-08-24      2013-08-24
4           2015-03-19      2015-03-19

---

For further reading on the poor scalability of recursive CTEs see the following series:

• Generate a set or sequence without loops – part 1
• Generate a set or sequence without loops – part 2
• Generate a set or sequence without loops – part 3

Answer 2

Return the row with the earliest date in the set Set that date as "current" Find the row with the earliest date more than "90" days after the current date Repeat from step 2 until no more rows are found

LINK for more details and information

Answer 3

As noted in the answer by GarethD recursion is not needed and might not be the best option. The different solutions produces the same result but the recursive performs a bit worse.

But anyway, for clarity, here is the recursive solution that finds the date that fulfills the two conditions: opened_dt + 2 years and less than todays date.

;WITH Test (CUSTOMER, OPENED, EXPIRED)
AS (
    SELECT A.CUSTOMER_NO, A.OPENED_DT, A.OPENED_DT AS EXPIRED
    FROM ACCOUNTS_MASTER A    
    UNION ALL 
    SELECT CUSTOMER, OPENED, DATEADD(YEAR,2, EXPIRED) 
    FROM TEST T 
    WHERE DATEADD(YEAR,2, EXPIRED) <= GETDATE() -- limiting condition that stops recursion
   )

SELECT CUSTOMER, OPENED, MAX(EXPIRED) EXPIRED 
FROM TEST 
GROUP BY CUSTOMER, OPENED
ORDER BY CUSTOMER;

This would generate a result like:

CUSTOMER    OPENED      EXPIRED
1           2010-08-02  2014-08-02
2           2013-04-27  2015-04-27
3           2013-08-24  2013-08-24
4           2015-03-19  2015-03-19

Note that all expired stops at the current date and never happens in the future - this might be what you want or not - the question was a bit unclear.