question

GPO avatar image
GPO asked

Puzzling query to join up events

I have an events table that captures (amongst other things) the start and end of an event and the person to whom the event occurs. When the end of one event and the beginning of another event for the same person occur on the same day, I want to join the events up and call them one big event. The new event would retain the event_id of the earliest event the event start of the earliest event and the event end of the latest event. One person can have any number of events. Some events for that person will require amalgamation, some won't. Here's some test data: IF OBJECT_ID('tempdb..#event') IS NOT NULL DROP TABLE #event; SELECT cast(1000 as int) as event_id ,cast(1 as int) as person_id ,cast('20100101 23:20:00' as smalldatetime)as event_start ,cast('20100103 14:10:00' as smalldatetime) as event_end INTO #event UNION ALL SELECT 1001,1,'20100103 20:20:00','20100106 07:20:00' UNION ALL SELECT 1002,1,'20100106 10:00:00','20100111 20:00:00' UNION ALL SELECT 1003,2,'20100109 13:20:00','20100109 22:00:00' UNION ALL SELECT 1004,2,'20100115 20:20:00','20100116 22:50:00' UNION ALL SELECT 1005,3,'20100102 11:00:00','20100103 12:30:00' UNION ALL SELECT 1006,4,'20100101 20:20:00','20100102 19:00:00' UNION ALL SELECT 1007,4,'20100102 20:50:00','20100103 18:30:00' UNION ALL SELECT 1008,4,'20100109 20:20:00','20100110 13:10:00' UNION ALL SELECT 1009,4,'20100110 04:00:00','20100111 23:00:00' The final result should look like this: event_id person_id event_start event_end 1000 1 1/Jan/10 23:20 11/Jan/10 20:00 1003 2 9/Jan/10 13:20 9/Jan/10 22:00 1004 2 15/Jan/10 20:20 16/Jan/10 22:50 1005 3 2/Jan/10 11:00 3/Jan/10 12:30 1006 4 1/Jan/10 20:20 3/Jan/10 18:30 1008 4 9/Jan/10 20:20 11/Jan/10 23:00 Note that events 1001 and 1002 have been incorporated into 1000. Event 1009 is now part of 1008, and event 1007 is part of 1006.
t-sqlrecursionrow_numberpuzzlewindowing-functions
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Andomar avatar image
Andomar answered
One approach is to create a list of `heads`, that is, events which start a chain. For each head, you then look for the next head. All events in between heads make part of the chain. In SQL: ; with heads as ( select * from #event ev1 where not exists ( select * from #event ev2 where ev1.event_id <> ev2.event_id and ev1.person_id = ev2.person_id and convert(varchar(10), ev1.event_start, 102) = convert(varchar(10), ev2.event_end, 102) ) ) select h1.event_id , h1.person_id , h1.event_start , lastevent.event_end from heads h1 outer apply ( select top 1 * from heads h2 where h1.person_id = h2.person_id and h1.event_start < h2.event_start order by h2.event_start ) nexthead outer apply ( select top 1 * from #event last where h1.person_id = last.person_id and h1.event_start <= last.event_start and ( last.event_start < nexthead.event_start or nexthead.event_start is null ) order by last.event_start desc ) lastevent The output matches the example output in the question.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Many thanks Andomar. Looks to be just what I need.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.