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)
or
-- 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
CodePudding user response:
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 rows unbounded preceding )
- A.Minutes
,B.StartDate)
,EndDate = dateadd(MINUTE
,sum(minutes) over (partition by A.EventID order by A.CauseID rows unbounded preceding )
,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