Limit SQL recursion to single row per recursion

April 5, 2022

I have two tables:

Event (EventId INT, StartDate DATETIME)

 EventId | StartDate
-----------------------------
 1000    | 2021-04-05 20:32:00

Cause (CauseId INT, EventId INT, Minutes INT)

 CauseId | EventId | Minutes
-----------------------------
 5000    | 1000    | 20
 5001    | 1000    | 27
 5002    | 1000    | 30

For each combination of EventId and CauseId I need to generate a StartDate and EndDate.

 EventId | CauseId | Minutes | StartDate           | EndDate
------------------------------------------------------------------
 1000    | 5000    | 20      | 2021-04-05 20:32:00 | 2021-04-05 20:52:00
 1000    | 5001    | 27      | 2021-04-05 20:52:00 | 2021-04-05 21:19:00
 1000    | 5002    | 30      | 2021-04-05 21:19:00 | 2021-04-05 21:49:00

LAG doesn’t seem like it would work, I would need to reference the previous rows calculation to generate the StartDate and the same rows calculation to generate the EndDate.

-- Not allowed.
SELECT e.EventId, c.CauseId, c.Minutes,
       LAG(CalculatedEndDate, 1, DATEADD(MI, c.Minutes, e.StartDate)) OVER (PARTITION BY e.EventId ORDER BY c.CauseId) AS CalculatedStartDate,
       DATEADD(MI, c.Minutes, CalculatedStartDate) AS CalculatedEndDate
FROM Event e
INNER JOIN Cause c ON c.EventId = e.EventId

A recursive CTE doesn’t seem to work either, I’m not allowed to use TOP or an AGGREGATE and don’t see how to handle the rows one at a time without it. e.g.

-- Initial query
UNION ALL
-- Removed for brevity
INNER JOIN cte ON cte.EventId = e.EventId
INNER JOIN Cause c ON c.CauseId  = (SELECT MIN(CauseId) FROM Cause WHERE CauseId > cte.CauseId AND EventId = e.EventId)

-- Initial query
UNION ALL
-- Removed for brevity
INNER JOIN cte ON cte.EventId = e.EventId
CROSS APPLY (SELECT TOP 1 *
             FROM Cause
             WHERE EventId = e.EventId AND CauseId > cte.CauseId
             ORDER BY CauseId ASC) c

>Solution :

Perhaps the window function sum() over() would help here

Example or dbFiddle

Select A.*
      ,StartDate = dateadd(MINUTE
                          ,sum(minutes) over (partition by A.EventID order by A.CauseID) 
                           - A.Minutes
                          ,B.StartDate)
     ,EndDate    = dateadd(MINUTE
                          ,sum(minutes) over (partition by A.EventID order by A.CauseID) 
                          ,B.StartDate)
 From Cause A
 Join Event B on A.EventID=B.EventID

Results

CauseId EventId Minutes StartDate               EndDate
5000    1000    20      2021-04-05 20:32:00.000 2021-04-05 20:52:00.000
5001    1000    27      2021-04-05 20:52:00.000 2021-04-05 21:19:00.000
5002    1000    30      2021-04-05 21:19:00.000 2021-04-05 21:49:00.000