Solved

# Find gaps in overlapping time periods

Posted on 2009-07-05

I have a table of events per person. Each event has a start date and a duration in days. Each person can have many events, and there are thousands of persons in the table. What I want to do, is, for each person, sum the number of days from the start of their first event to the end of their last event, where the end of an event is startdate + duration. The kicker is, if there are gaps within that time period (intervals - in days - not covered by any event, I want to know how many gaps there are, and the total gap time, in days.

So if person 123 has 2 events:

3/1/2008 for 10 days, 4/1/2008 for 20 days, then the result for person 123 would look like:

total duration: 51 days (from 3/1/2008 to 4/20/2008)

gaps: 1

gap days: 21 (from 3/11/2008 to 3/31/2008)

If person 456 has 3 events:

3/1/2008 for 10 days, 3/12/2008 for 10 days, 4/1/2008 for 20 days, then the result for person 456 would look like:

total duration: 51 days (from 3/1/2008 to 4/20/2008)

gaps: 2

gap days: 11 (3/11/2008, and from 3/22/2008 to 3/31/2008)

The table looks like: create table person_events(

person_id int,

event_start datetime,

event_duration int)

Assume no person has more than 1 event that starts on the same day, if that makes any difference. Of course, different persons may have events that start on the same day.