Skip to main content
deleted 108 characters in body
Source Link
Caleb Carl
  • 328
  • 2
  • 6
WITH x
AS
(
    SELECT EventID
         , MAX(EndTime)StartTime
 AS eID       , EndTime
         , StartTimeIIF(
    FROM EventTimes
    GROUP      starttime <= MAX(EndTime) OVER (PARTITION BY EventID
           , StartTime
)
   , y
AS
(
    SELECT EventID
         , MIN(x.StartTime) AS stID
         , x.eID
    FROMORDER xBY StartTime
    GROUP BY x.EventID
           , x.eID
)
   , z
AS
(
    SELECT y.EventID
         , y.stID
         , y.eIDROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
         , IIF((
                   LAG(y.stID) OVER (ORDER BY y.EventID, y.stID DESC) <= y.eID
         )
          AND LAG    OR endtime >= MIN(y.EventIDstarttime) OVER (ORDERPARTITION BY y.EventID, 
 y.stID DESC) = y.EventID
               )
               OR
               ( ORDER BY StartTime
                   LEAD(y.eID) OVER (ORDER BY y.EventID, y.stID DESC) >= y.stID
                   AND LEAD(y.EventID) OVER (ORDER BYROWS y.EventID,BETWEEN y.stID1 DESC)FOLLOWING =AND y.EventIDUNBOUNDED FOLLOWING
                                                 )
             , 1
             , 0) AS grp
    FROM ydbo.EventTimes
)
   , kj
AS
(
    SELECT z.EventID
         , MIN(z.stIDstarttime) AS stID
         , MAX(z.eIDendtime) AS eID
    FROM zx
    WHERE zx.grp = 1
    GROUP BY zx.EventID
           , zx.grp
    UNION ALL
    SELECT zx.EventID
         , zx.starttime AS stID
         , zx.endtime AS eID
    FROM zx
    WHERE zx.grp = 0
)
SELECT EventID
     , CAST(SUM(DATEDIFF(mi, kj.stID, kj.eID)) AS DECIMAL(5, 1)) / 60 AS EventTime
FROM kj
GROUP BY EventID;

DB Fiddle for referenceDB Fiddle for reference

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.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;

DB Fiddle for reference

WITH x
AS
(
    SELECT EventID
         , StartTime
         , EndTime
         , IIF(
               starttime <= MAX(EndTime) OVER (PARTITION BY EventID
                                               ORDER BY StartTime
                                               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                                              )
               OR endtime >= MIN(starttime) OVER (PARTITION BY EventID 
                                                  ORDER BY StartTime
                                                  ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
                                                 )
             , 1
             , 0) AS grp
    FROM dbo.EventTimes
)
   , j
AS
(
    SELECT EventID
         , MIN(starttime) AS stID
         , MAX(endtime) AS eID
    FROM x
    WHERE x.grp = 1
    GROUP BY x.EventID
           , x.grp
    UNION ALL
    SELECT x.EventID
         , x.starttime AS stID
         , x.endtime AS eID
    FROM x
    WHERE x.grp = 0
)
SELECT EventID
     , CAST(SUM(DATEDIFF(mi, j.stID, j.eID)) AS DECIMAL(5)) / 60 AS EventTime
FROM j
GROUP BY EventID;

DB Fiddle for reference

deleted 170 characters in body
Source Link
Caleb Carl
  • 328
  • 2
  • 6

This particular solution treats time ranges that edge each other as non-overlapping, though argument could be made that they are. (e.g. 2:30 - 4:00 and 4:00 - 14:00).

This particular solution treats time ranges that edge each other as non-overlapping, though argument could be made that they are. (e.g. 2:30 - 4:00 and 4:00 - 14:00).

deleted 31 characters in body
Source Link
Caleb Carl
  • 328
  • 2
  • 6
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;
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;
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.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;
Incorporated comment
Source Link
Paul White
  • 90.3k
  • 30
  • 424
  • 663
Loading
deleted 39 characters in body
Source Link
Caleb Carl
  • 328
  • 2
  • 6
Loading
deleted 173 characters in body
Source Link
Caleb Carl
  • 328
  • 2
  • 6
Loading
edited body
Source Link
Caleb Carl
  • 328
  • 2
  • 6
Loading
added 90 characters in body
Source Link
Caleb Carl
  • 328
  • 2
  • 6
Loading
added 25 characters in body
Source Link
Caleb Carl
  • 328
  • 2
  • 6
Loading
deleted 69 characters in body
Source Link
Caleb Carl
  • 328
  • 2
  • 6
Loading
Source Link
Caleb Carl
  • 328
  • 2
  • 6
Loading