Removing partially/fully overlapping events with start and end timestamps across multiple values

Question

How would the accepted answer change if the events table contained a "Room" column with multiple values and you wanted to take out the overlapping meetings by room? The table could look like:

id   start                   end                     created_at                  room
1    2019-01-23 18:30:00.0   2019-01-23 19:00:00.0   2019-01-18 21:28:27.427612  Room1
2    2019-01-23 18:30:00.0   2019-01-23 19:00:00.0   2019-01-23 01:04:05.861876  Room1
3    2019-01-23 18:00:00.0   2019-01-23 18:45:00.0   2019-01-16 17:14:50.709552  Room1
4    2019-01-23 18:30:00.0   2019-01-23 19:30:00.0   2019-01-22 19:24:05.532491  Room1
5    2019-01-23 18:30:00.0   2019-01-23 19:30:00.0   2019-01-18 17:28:40.074205  Room1
6    2019-01-23 20:00:00.0   2019-01-23 20:30:00.0   2019-01-18 15:22:30.736888  Room1
7    2019-01-23 20:15:00.0   2019-01-23 20:45:00.0   2019-01-20 20:20:20.202020  Room1
8    2019-01-23 18:30:00.0   2019-01-23 19:00:00.0   2019-01-18 21:28:27.427612  Room2
9    2019-01-23 18:30:00.0   2019-01-23 19:00:00.0   2019-01-23 01:04:05.861877  Room2
10   2019-01-23 18:00:00.0   2019-01-23 18:45:00.0   2019-01-16 17:14:50.709552  Room2
11   2019-01-23 18:30:00.0   2019-01-23 19:30:00.0   2019-01-22 19:24:05.532491  Room2
12   2019-01-23 18:30:00.0   2019-01-23 19:30:00.0   2019-01-18 17:28:40.074205  Room2
13   2019-01-23 20:00:00.0   2019-01-23 20:30:00.0   2019-01-18 15:22:30.736888  Room2
14   2019-01-23 20:15:00.0   2019-01-23 20:45:00.0   2019-01-20 20:20:20.202020  Room2
15   2019-01-23 20:00:00.0   2019-01-23 20:30:00.0   2019-01-18 15:22:30.736888  Room3
16   2019-01-23 20:15:00.0   2019-01-23 20:45:00.0   2019-01-20 20:20:20.202021  Room3

With the end result being:

id   start                   end                     created_at                  room
2    2019-01-23 18:30:00.0   2019-01-23 19:00:00.0   2019-01-23 01:04:05.861876  Room1
7    2019-01-23 20:15:00.0   2019-01-23 20:45:00.0   2019-01-20 20:20:20.202020  Room1
9    2019-01-23 18:30:00.0   2019-01-23 19:00:00.0   2019-01-23 01:04:05.861877  Room2
14   2019-01-23 20:15:00.0   2019-01-23 20:45:00.0   2019-01-20 20:20:20.202020  Room2
16   2019-01-23 20:15:00.0   2019-01-23 20:45:00.0   2019-01-20 20:20:20.202021  Room3

The answer from the previous question below can only give the latest created event in the time block across ALL rooms, not by room.

select max(id), min(start), max(end), max(created_at)
from (select t.*,
             count(*) filter (where max_end < end) over (order by start) as grouping
      from (select t.*,
                   max(end) over (order by start rows between unbounded preceding and 1 preceding) as max_end
            from events t
           ) t
     ) t
group by grouping;

Can you explain the logic for the desire output? Because i see some 19:30 for room1 just ignored. — Juan Carlos Oropeza, Jul 16 '19 at 19:33
@JuanCarlosOropeza the logic is: Group all events that overlap together, and show the one that has the latest created_at. There are 5 events that overlap between the time block 18:00-19:30 in room 1. out of those five, the event with the latest created_at goes from 18:30-19:00. — hov, Jul 16 '19 at 20:11

PeterHe · Answer 1 · 2019-07-17T14:23:23.477

The first cte cte_e orders the data. The second one cte_group gets the number of overlapping groups. The third one cte_range is recursive and to get the max end time of the overlapping group. The last cte just get all the records in each overlapping group and get the max created_at time.

;WITH RECURSIVE cte_e
AS
(
  SELECT *, row_number() OVER (PARTITION BY room ORDER BY start,"end") AS row_id
  FROM events
),
cte_group
AS
(
  SELECT c.id,c."start",c."end",c.room,c.row_id,n."end" AS next_end
  FROM cte_e c
  LEFT JOIN cte_e n
  ON c.room=n.room
  AND c.row_id=n.row_id-1
  LEFT JOIN cte_e p
  ON p.room=c.room
  AND p.row_id=c.row_id-1
  WHERE (p."end" IS NULL OR p."end"<c."start")
  AND c."end" > n."start"
),
cte_range
AS
(
  SELECT *
  FROM cte_group
UNION ALL
  SELECT c.id,c."start",c."end",c.room,c.row_id,e."end" AS next_end
  FROM cte_group c
  LEFT JOIN cte_e e
  ON c.room=e.room
  AND c.row_id<e.row_id
  AND c."end">e."start"
  WHERE e."end" IS NOT NULL
 ),
 cte_max AS
 (
   SELECT g.room,MAX(e.created_at) AS created_at
   FROM cte_range g 
   INNER JOIN events e
   ON g.room=e.room
   AND g."start"<e."end"
   AND g."end">e."start"
   GROUP BY g.room,g.row_id
)
SELECT e.*
FROM events e
INNER JOIN cte_max m
ON e.room=m.room
AND e.created_at=m.created_at;

www.sqlfiddle.com/#!17/75d02/1/0 - i'm getting an empty output — hov, Jul 16 '19 at 20:23
So you have multiple overlapping sets for each room, and you want to get the last record for each overlapping group for each room. — PeterHe, Jul 17 '19 at 14:20

score 1 · Accepted Answer · answered Jul 16 '19 at 20:48

The join condition:

Try to found another event with same room
But need to have a different id than you
Must overlap dates Determine Whether Two Date Ranges Overlap
Finally must have a later created date.

If you can't find any other row mean you are alone either because don't overlap or because you are the latest date.

SQL DEMO

SELECT a.*
FROM "events" a
LEFT JOIN "events" b
  ON a.room = b.room 
 AND a.id <> b.id
 AND a."start" <= b."end"
 AND a."end"   >= b."start"
 AND a.created_at < b.created_at
WHERE b.id IS NULL;

OUTPUT

| id |                start |                  end |                  created_at |  room |
|----|----------------------|----------------------|-----------------------------|-------|
|  2 | 2019-01-23T18:30:00Z | 2019-01-23T19:00:00Z | 2019-01-23T01:04:05.861876Z | Room1 |
|  7 | 2019-01-23T20:15:00Z | 2019-01-23T20:45:00Z |  2019-01-20T20:20:20.20202Z | Room1 |
|  9 | 2019-01-23T18:30:00Z | 2019-01-23T19:00:00Z | 2019-01-23T01:04:05.861877Z | Room2 |
| 14 | 2019-01-23T20:15:00Z | 2019-01-23T20:45:00Z |  2019-01-20T20:20:20.20202Z | Room2 |
| 16 | 2019-01-23T20:15:00Z | 2019-01-23T20:45:00Z | 2019-01-20T20:20:20.202021Z | Room3 |

Removing partially/fully overlapping events with start and end timestamps across multiple values

2 Answers2

Linked