x

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.

more ▼

asked Jul 17, 2011 at 04:16 AM in Default

avatar image

GPO
4.9k 42 52 58

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Jul 17, 2011 at 08:50 AM

avatar image

Andomar
330 3 5 10

Many thanks Andomar. Looks to be just what I need.

Jul 18, 2011 at 07:53 PM GPO
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1096
x25
x10
x8
x3

asked: Jul 17, 2011 at 04:16 AM

Seen: 1274 times

Last Updated: Dec 11, 2016 at 06:11 AM

Copyright 2018 Redgate Software. Privacy Policy