Recursive query to use a date returned in initial query as limit in subsequent query

Question

I have a business need to project when a specific task needs to be done based on the usage of a task.
For example, you need to change the oil in your car every 3000 miles. Some days you drive 300 miles, and other days you drive 500 miles. When you hit 3000, you change the oil, and restart the counter. Based on a projected usage table, return a set of all the oil change dates.

I could do this in a table-valued function or some other 'coded' solution.
But I thought I could do it in one statement, a recursive cte perhaps.
I'm having difficulties 'joining' the next date into the WHERE of the recursive part.
And SQL doesn't like 'TOP 1' in a recursive CTE at all. :)

I would like a set like this:

This is what I've got:

WITH cte_MilesMX (RateDate,RunningRateMiles)
AS
(
    -- Initial query
    SELECT TOP 1 *
    FROM (
      SELECT 
      RateDate,
      SUM(RateMiles) OVER (ORDER BY RateDate) AS RunningRateMiles
      FROM dbo.RatesbyDay
      WHERE RateDate > '2020-01-01') q1
    WHERE q1.RunningRateMiles >= 3000
    UNION ALL
    -- Recursive part
    SELECT TOP 1 *
    FROM (
      SELECT 
      rbd.RateDate,
      SUM(RateMiles) OVER (ORDER BY rbd.RateDate) AS RunningRateMiles
      FROM dbo.RatesbyDay rbd
      JOIN cte_MilesMX cte
        ON 1 = 1
      WHERE rbd.RateDate > cte.RateDate) q1
    WHERE q1.RunningRateMiles >= 3000
)

SELECT *
FROM   cte_MilesMX

If you want to fool with this, here is the example:
Any help would be greatly appreciated. Thanks.

CREATE TABLE RatesbyDay(
    RateDate DATE,
    RateMiles INT);
INSERT INTO RatesbyDay VALUES ('2020-01-01',600)
INSERT INTO RatesbyDay VALUES ('2020-01-02',450)
INSERT INTO RatesbyDay VALUES ('2020-01-03',370)
INSERT INTO RatesbyDay VALUES ('2020-01-04',700)
INSERT INTO RatesbyDay VALUES ('2020-01-05',100)
INSERT INTO RatesbyDay VALUES ('2020-01-06',480)
INSERT INTO RatesbyDay VALUES ('2020-01-07',430)
INSERT INTO RatesbyDay VALUES ('2020-01-08',200)
INSERT INTO RatesbyDay VALUES ('2020-01-09',590)
INSERT INTO RatesbyDay VALUES ('2020-01-10',380)
INSERT INTO RatesbyDay VALUES ('2020-01-11',220)
INSERT INTO RatesbyDay VALUES ('2020-01-12',320)
INSERT INTO RatesbyDay VALUES ('2020-01-13',360)
INSERT INTO RatesbyDay VALUES ('2020-01-14',600)
INSERT INTO RatesbyDay VALUES ('2020-01-15',450)
INSERT INTO RatesbyDay VALUES ('2020-01-16',475)
INSERT INTO RatesbyDay VALUES ('2020-01-17',300)
INSERT INTO RatesbyDay VALUES ('2020-01-18',190)
INSERT INTO RatesbyDay VALUES ('2020-01-19',435)
INSERT INTO RatesbyDay VALUES ('2020-01-20',285)
INSERT INTO RatesbyDay VALUES ('2020-01-21',350)
INSERT INTO RatesbyDay VALUES ('2020-01-22',410)
INSERT INTO RatesbyDay VALUES ('2020-01-23',250)
INSERT INTO RatesbyDay VALUES ('2020-01-24',300)
INSERT INTO RatesbyDay VALUES ('2020-01-25',250)
INSERT INTO RatesbyDay VALUES ('2020-01-26',650)
INSERT INTO RatesbyDay VALUES ('2020-01-27',180)
INSERT INTO RatesbyDay VALUES ('2020-01-28',280)
INSERT INTO RatesbyDay VALUES ('2020-01-29',200)
INSERT INTO RatesbyDay VALUES ('2020-01-30',100)
INSERT INTO RatesbyDay VALUES ('2020-01-31',100)

-- this returns the 1st oil change assuming we just changed it on 1-1-2020
SELECT TOP 1 *
FROM (
  SELECT 
    RateDate,
    SUM(RateMiles) OVER (ORDER BY RateDate) AS RunningRateMiles
FROM dbo.RatesbyDay
WHERE RateDate > '2020-01-01') q1
WHERE q1.RunningRateMiles >= 3000

-- the above query returned 1-9-2020 as the oil change, so when is the next one.
SELECT TOP 1 *
FROM (
  SELECT 
    RateDate,
    SUM(RateMiles) OVER (ORDER BY RateDate) AS RunningRateMiles
FROM dbo.RatesbyDay
WHERE RateDate > '2020-01-09') q1
WHERE q1.RunningRateMiles >= 3000

-- etc. etc.
SELECT TOP 1 *
FROM (
  SELECT 
    RateDate,
    SUM(RateMiles) OVER (ORDER BY RateDate) AS RunningRateMiles
FROM dbo.RatesbyDay
WHERE RateDate > '2020-01-17') q1
WHERE q1.RunningRateMiles >= 3000

SELECT TOP 1 *
FROM (
  SELECT 
    RateDate,
    SUM(RateMiles) OVER (ORDER BY RateDate) AS RunningRateMiles
FROM dbo.RatesbyDay
WHERE RateDate > '2020-01-26') q1
WHERE q1.RunningRateMiles >= 3000

user_8233079 · Answer

This isn't a recursive CTE but it does do what you're what you're trying to do. The technique goes by a couple different names... Usually either "Quirky Update" or "Ordered Update".

First thing, notice that I added two new columns to your table and a clustered index. They are in fact necessary but if are unwilling or unable to modify the existing table, this works just as well with a #TempTable.

For more detailed information, see Solving the Running Total and Ordinal Rank Problems (Rewritten)

Also... fair warning, this technique isn't without it's detractors due to the fact that Microsoft doesn't guarantee that it will work as expected.

USE tempdb;
GO

IF OBJECT_ID('tempdb.dbo.RatesByDay', 'U') IS NOT NULL 
BEGIN DROP TABLE tempdb.dbo.RatesByDay; END;
GO

CREATE TABLE tempdb.dbo.RatesByDay (
    RateDate date NOT NULL
        CONSTRAINT pk_RatesByDay PRIMARY KEY CLUSTERED (RateDate), -- clustered index is needed to control the direction of the update.
    RateMiles int NOT NULL,
    IsChangeDay bit NULL,
    MilesSinceLastChange int NULL
    );
GO

INSERT tempdb.dbo.RatesByDay (RateDate, RateMiles) VALUES
    ('2020-01-01',600),('2020-01-02',450),('2020-01-03',370),('2020-01-04',700),('2020-01-05',100),('2020-01-06',480),
    ('2020-01-07',430),('2020-01-08',200),('2020-01-09',590),('2020-01-10',380),('2020-01-11',220),('2020-01-12',320),
    ('2020-01-13',360),('2020-01-14',600),('2020-01-15',450),('2020-01-16',475),('2020-01-17',300),('2020-01-18',190),
    ('2020-01-19',435),('2020-01-20',285),('2020-01-21',350),('2020-01-22',410),('2020-01-23',250),('2020-01-24',300),
    ('2020-01-25',250),('2020-01-26',650),('2020-01-27',180),('2020-01-28',280),('2020-01-29',200),('2020-01-30',100),
    ('2020-01-31',100);

--=====================================================================================================================

DECLARE 
    @RunningMiles int = 0,
    @Anchor date;

UPDATE rbd SET          
    @RunningMiles = rbd.MilesSinceLastChange = CASE WHEN @RunningMiles < 3000 THEN @RunningMiles ELSE 0 END + rbd.RateMiles,
    rbd.IsChangeDay = CASE WHEN @RunningMiles < 3000 THEN 0 ELSE 1 END,
    @Anchor = rbd.RateDate
FROM
    dbo.RatesByDay rbd WITH (TABLOCKX, INDEX (1))
WHERE 1 = 1
    AND rbd.RateDate > '2020-01-01'
OPTION (MAXDOP 1);

-------------------------------------

SELECT * FROM dbo.RatesByDay rbd;

And the results...

RateDate   RateMiles   IsChangeDay MilesSinceLastChange
---------- ----------- ----------- --------------------
2020-01-01 600         NULL        NULL
2020-01-02 450         0           450
2020-01-03 370         0           820
2020-01-04 700         0           1520
2020-01-05 100         0           1620
2020-01-06 480         0           2100
2020-01-07 430         0           2530
2020-01-08 200         0           2730
2020-01-09 590         1           3320
2020-01-10 380         0           380
2020-01-11 220         0           600
2020-01-12 320         0           920
2020-01-13 360         0           1280
2020-01-14 600         0           1880
2020-01-15 450         0           2330
2020-01-16 475         0           2805
2020-01-17 300         1           3105
2020-01-18 190         0           190
2020-01-19 435         0           625
2020-01-20 285         0           910
2020-01-21 350         0           1260
2020-01-22 410         0           1670
2020-01-23 250         0           1920
2020-01-24 300         0           2220
2020-01-25 250         0           2470
2020-01-26 650         1           3120
2020-01-27 180         0           180
2020-01-28 280         0           460
2020-01-29 200         0           660
2020-01-30 100         0           760
2020-01-31 100         0           860

user_10676716 · Answer

You can do this with a recursive query:

with 
    data as (select r.*, row_number() over(order by ratedate) rn from ratesbyday r),
    cte as (
        select d.*, ratemiles total, ratemiles newtotal from data d where rn = 1
        union all
        select d.*, 
            c.newtotal + d.ratemiles,
            case when c.newtotal < 3000 and c.newtotal + d.ratemiles >= 3000 then 0 else c.newtotal + d.ratemiles end
        from cte c
        inner join data d on d.rn = c.rn + 1
    )
select ratedate, ratemiles, total 
from cte 
where newtotal = 0
order by ratedate

The query starts by enumerating the rows. Then, it iteratively walks them, starting from the "first" one; everytime we exceed the 3000 miles threshold, we reset the running miles count. We can then filter on "reset" rows.

Demo on DB Fiddle:

ratedate   | ratemiles | total
:--------- | --------: | ----:
2020-01-07 |       430 |  3130
2020-01-15 |       450 |  3120
2020-01-25 |       250 |  3245

If there may be more than 100 rows in your dataset, you need to add option (maxrecursion 0) at the very end of the query.

user_890087 · Answer

In this instance I would use a rolling agg and then use the mod operator to find the points where it hits the 3000 interval.

Using the table desc and inserts above here is an example:

-- When the mod value "resets" then the oil change is due, check this using LAG
SELECT 
agg.RateDate
,agg.RateMiles
,agg.MilesAgg
,agg.MilesAgg%3000 AS ModValue
,CASE WHEN agg.MilesAgg%3000 < LAG(agg.MilesAgg) OVER(ORDER BY agg.RateDate)%3000
THEN 'Due'
ELSE 'NotDue'
END

FROM
(
--Get the rolling total of miles
SELECT
rbd.RateDate
,rbd.RateMiles
,SUM(rbd.RateMiles) OVER(ORDER BY rbd.RateDate ROWS UNBOUNDED PRECEDING) AS MilesAgg
FROM #RatesByDay rbd
) agg

Results, first day is counting the 600 miles as being AFTER the oil change

RateDate    Mi  MiAgg   Mod     IsDue?
--------------------------------------
2020-01-01  600 600     600     NotDue
2020-01-02  450 1050    1050    NotDue
2020-01-03  370 1420    1420    NotDue
2020-01-04  700 2120    2120    NotDue
2020-01-05  100 2220    2220    NotDue
2020-01-06  480 2700    2700    NotDue
2020-01-07  430 3130    130     Due
2020-01-08  200 3330    330     NotDue
2020-01-09  590 3920    920     NotDue
2020-01-10  380 4300    1300    NotDue
2020-01-11  220 4520    1520    NotDue
2020-01-12  320 4840    1840    NotDue
2020-01-13  360 5200    2200    NotDue
2020-01-14  600 5800    2800    NotDue
2020-01-15  450 6250    250     Due
2020-01-16  475 6725    725     NotDue
2020-01-17  300 7025    1025    NotDue
2020-01-18  190 7215    1215    NotDue
2020-01-19  435 7650    1650    NotDue
2020-01-20  285 7935    1935    NotDue
2020-01-21  350 8285    2285    NotDue
2020-01-22  410 8695    2695    NotDue
2020-01-23  250 8945    2945    NotDue
2020-01-24  300 9245    245     Due
2020-01-25  250 9495    495     NotDue
2020-01-26  650 10145   1145    NotDue
2020-01-27  180 10325   1325    NotDue
2020-01-28  280 10605   1605    NotDue
2020-01-29  200 10805   1805    NotDue
2020-01-30  100 10905   1905    NotDue
2020-01-31  100 11005   2005    NotDue

Recursive query to use a date returned in initial query as limit in subsequent query

3 Answers