CODEWITHSUNDEEP
sql
sXe
sXe

Reputation: 242

How to get a count of records by unique time spans?

I've got a table that has a series of start dates and end dates, and I'm trying to figure out how many unique sessions exist--in other words, how many distinct time periods that don't overlap.

So if the table looks like this:

ID   |   StartTime   |   EndTime
 A   |  1/1/11 12:00 | 1/1/11 14:00
 B   |  1/1/11 12:30 | 1/1/11 15:00
 C   |  1/1/11 14:30 | 1/1/11 15:30
 D   |  1/1/11 16:00 | 1/1/11 16:30
 E   |  1/1/11 16:30 | 1/1/11 17:00

what kind of query would return a count of 3 sessions since records A, B and C overlap?

Upvotes: 1

Views: 149

Answers (1)

JNK
JNK

Reputation: 65147

Try this. Returns correct count in SQL Server 2008.

DECLARE @t table (id char(1), Starttime smalldatetime, endtime smalldatetime)

INSERT INTO @t
VALUES
('A','1/1/11 12:00','1/1/11 14:00'),
('B','1/1/11 12:30','1/1/11 15:00'),
('C','1/1/11 14:30','1/1/11 15:30'),
('D','1/1/11 16:00','1/1/11 16:30'),
('E','1/1/11 16:30','1/1/11 17:00')


select 
  count(t.id)
from
  @t t
WHERE NOT EXISTS (  SELECT 1 FROM @t t2
                    WHERE (t.starttime > t2.starttime
                    AND t.endtime < t2.starttime)
                    OR (t.starttime < t2.starttime
                    AND t.endtime > t2.starttime) )

Upvotes: 2

Related Questions