T-SQL recursion, date shifting based on previous iteration

Question

I have a data set that includes a customer, payment date, and the number of days they have paid for. I need to be calculate the coverage start/end dates that each payment is covering. This is difficult when a payment is made before the current coverage period ends.

The best way I've come up with to think about this would be a month to month cell phone plan where the customer may pay for a specified number of days at any point during a given month. The next covered period should always start the day after the previous covered period expires.

Here is the code sample using a temp table.

CREATE TABLE #Payments 
(Customer_ID INTEGER, 
 Payment_Date DATE,
 Days_Paid INTEGER);

INSERT INTO #Payments
VALUES (1,'2018-01-01',30);

INSERT INTO #Payments
VALUES (1,'2018-01-29',20);

INSERT INTO #Payments
VALUES (1,'2018-02-15',30);

INSERT INTO #Payments
VALUES (1,'2018-04-01',30);

I need to get the coverage start/end dates back.

The initial payment is made on 2018-01-01 and they paid for 30 days. That means they are covered until 2018-01-30 (Payment_Date + Paid_Days - 1 since the payment date is included as a covered day). However they made their next payment on 2018-01-29, so I need calculate the start date of the next coverage window, which in this case would be the previous Payment_Date + previous Paid_Days. In this case, coverage window 2 starts on 2018-02-01 and would extend through the 2018-02-19 since they only paid for 20 days on Payment_Date 2018-01-29.

The expected output is:

Customer_ID | Payment_Date | Days_Paid | Coverage_Start_Date | Coverage_End_Date
--------------------------------------------------------------------------------
1           |  '2018-01-01'|        30 |         '2018-01-01'|      '2018-01-30'
1           |  '2018-01-29'|        20 |         '2018-01-31'|      '2018-02-19' 
1           |  '2018-02-15'|        30 |         '2018-02-20'|      '2018-03-21'
1           |  '2018-04-01'|        30 |         '2018-04-01'|      '2018-04-30'

Because the current record's coverage start date will depend of the previous record's coverage end date, I feel like this would be a good candidate for recursion, but I can't figure out how to do it.

I have a way to do this in a while loop, but I would like to complete it using a recursive CTE. I have also thought about simply adding up the Days_Paid and adding that to the first payment's start date, however this only works if a payment is made before the previous coverage has expired. In addition, I need to calculate the coverage start/end dates for each Payment_Date.

Finally, using LAG/LEAD functions doesn't appear to work because it does not consider the result of the previous iteration, only the current value of the previous record. Using LAG/LEAD, you get the correct answer for the 2nd payment record, but not the third.

Is there a way to do this with a recursive CTE?

Answer 1

NOTE: This is not a recursive solution, but it is set-based vs. your loop solution.

While trying to solve this recursively it hit me that this is essentially a "running totals" problem, and can be easily solved with window functions.

WITH runningTotal AS 
(
    SELECT p.*, SUM(Days_Paid) OVER(ORDER BY p.Payment_Date) AS runningTotalDays, MIN(Payment_Date) OVER(ORDER BY p.Payment_Date) startDate
    FROM #Payments p 
)
SELECT r.Customer_Id, r.Payment_Date,Days_Paid, COALESCE(DATEADD(DAY, LAG(runningTotalDays) OVER(ORDER BY r.Payment_Date) +1, startDate), startDate) AS Coverage_Start_Date, DATEADD(DAY, runningTotalDays, startDate) AS Coverage_End_Date
FROM runningTotal r

Each end date is the "running total" of all the previous Days_Paid added together. Using LAG to get the previous records end date+1 gets you the start date. The COALESCE is to handle the first record. For more than a single customer, you can PARTITION BY Customer_Id.

Answer 2

So of course, right after posting this I came across a similar question that was already answered.

Here's the link: Recursively retrieve LAG() value of previous record

Based on that solution, I was able construct the following solution to my own question.

The key here was adding the "prep_data" CTE which made the recursion problem much easier.

 ;WITH prep_data AS
        (SELECT Customer_ID,
                ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Payment_Date) AS payment_seq_num,
                Payment_Date,
                Days_Paid,
                Payment_Date as Coverage_Start_Date,
                DATEADD(DAY,Days_Paid-1,Payment_Date) AS Coverage_End_Date
          FROM #Payments),
    recursion AS
        (SELECT Customer_ID,
                payment_seq_num,
                Payment_Date,
                Days_Paid,
                Coverage_Start_Date,
                Coverage_End_Date
           FROM prep_data
           WHERE payment_seq_num = 1
           UNION ALL
         SELECT r.Customer_ID,
                p.payment_seq_num,
                p.Payment_Date,
                p.Days_Paid,
                CASE WHEN r.Coverage_End_Date >= p.Payment_Date THEN DATEADD(DAY,1,r.Coverage_End_Date) ELSE p.Payment_Date END AS Coverage_Start_Date,
                DATEADD(DAY,p.Days_Paid-1,CASE WHEN r.Coverage_End_Date >= p.Payment_Date THEN DATEADD(DAY,1,r.Coverage_End_Date) ELSE p.Payment_Date END) AS Coverage_End_Date
           FROM recursion r 
                JOIN prep_data p ON r.payment_seq_num + 1 =p.payment_seq_num
        )
SELECT Customer_ID, 
       Payment_Date, 
       Days_Paid, 
       Coverage_Start_Date, 
       Coverage_End_Date 
  FROM recursion
ORDER BY payment_seq_num;