WITH x
AS
(
SELECT EventID
, MAX(EndTime) AS eID
, StartTime
FROM EventTimes
GROUP BY EventID
, StartTime
)
, y
AS
(
SELECT EventID
, MIN(x.StartTime) AS stID
, x.eID
FROM x
GROUP BY x.EventID
, x.eID
)
, z
AS
(
SELECT y.stID DESC) AS id
, y.EventID
, y.stID
, y.eID
, IIF((
LAG(y.stID) OVER (ORDER BY y.EventID, y.stID DESC) <= y.eID
AND LAG(y.EventID) OVER (ORDER BY y.EventID, y.stID DESC) = y.EventID
)
OR
(
LEAD(y.eID) OVER (ORDER BY y.EventID, y.stID DESC) >= y.stID
AND LEAD(y.EventID) OVER (ORDER BY y.EventID, y.stID DESC) = y.EventID
)
, 1
, 0) AS grp
FROM y
)
, k
AS
(
SELECT z.EventID
, MIN(z.stID) AS stID
, MAX(z.eID) AS eID
FROM z
WHERE z.grp = 1
GROUP BY z.EventID
, z.grp
UNION ALL
SELECT z.EventID
, z.stID
, z.eID
FROM z
WHERE z.grp = 0
)
SELECT EventID
, CAST(SUM(DATEDIFF(mi, k.stID, k.eID)) AS DECIMAL(5, 1)) / 60 AS EventTime
FROM k
GROUP BY EventID;