Summing "masked" datetime ranges using overlapping datetimes in SQL Server

Question

I'm having trouble summing up date differences (in minutes) across overlapping date times, using the second set of datetimes as more of a "mask".

WHAT I HAVE

My table might look something like this:

ID         StartDateTime           EndDateTime            Type
1          9-16-2013 12:00:00      9-16-2013 16:00:00     A      
2          9-16-2013 12:00:00      9-16-2013 13:00:00     A
3          9-16-2013 14:00:00      9-16-2013 15:00:00     A
4          9-16-2013 12:00:00      9-16-2013 13:00:00     C
5          9-16-2013 14:00:00      9-16-2013 17:00:00     C

A visual timeline would look like this:

      12      13      14      15      16      17
A     |-------------------------------|                    (ID 1)
A     |-------|       |-------|                            (IDs 2 and 3)
C     |-------|       |-----------------------|

WHAT I NEED

What I need to do is sum type A only if type B. So the set I'm looking to generate and sum is (ID included for reference):

ID         StartDateTime           EndDateTime
1          9-16-2013 12:00:00      9-16-2013 13:00:00
1          9-16-2013 14:00:00      9-16-2013 16:00:00
2          9-16-2013 12:00:00      9-16-2013 13:00:00
3          9-16-2013 14:00:00      9-16-2013 15:00:00

The new "masked" timeline would look like this:

      12      13      14      15      16      17
A     |-------|       |---------------|                    (ID 1)
A     |-------|       |-------|                            (ID 2 and 3)

The problem that I am having is splitting the first ID into 2 different records because of the break in type C. I've looked at a lot of the overlap posts on this site, but none seem to address this particular need. Any help would be greatly appreciated. Please let me know if you need more information or if I can be clearer in any way. I'm using SQL Server 2008 R2.

your question is unclear, where is hour from 13 to 14 of 1 ID ? — Justin, Sep 25 '13 at 06:05

score 1 · Accepted Answer · answered Sep 25 '13 at 07:51

One possible solution:

--set up test data
DECLARE @t TABLE
(ID INT,StartDateTime DATETIME,EndDateTime DATETIME,[Type] CHAR(1))

INSERT @t
(ID,StartDateTime,EndDateTime,[Type])
VALUES
(1,'9-16-2013 12:00:00','9-16-2013 16:00:00','A'),
(2,'9-16-2013 12:00:00','9-16-2013 13:00:00','A'),
(3,'9-16-2013 14:00:00','9-16-2013 15:00:00','A'),
(4,'9-16-2013 12:00:00','9-16-2013 13:00:00','C'),
(5,'9-16-2013 14:00:00','9-16-2013 17:00:00','C')

-- solution
SELECT  a.Id,
        CASE WHEN a.StartDateTime < c.StartDateTime THEN c.StartDateTime ELSE a.StartDateTime END AS StartDateTime,
        CASE WHEN a.EndDateTime < c.EndDateTime THEN a.EndDateTime ELSE c.EndDateTime END AS EndDateTime,
        a.[Type]
FROM    (SELECT * FROM @t WHERE [Type] = 'A') AS a
JOIN    (SELECT * FROM @t WHERE [Type] = 'C') AS c
ON      c.StartDateTime < a.EndDateTime
AND     c.EndDateTime > a.StartDateTime 
ORDER BY a.Id, a.StartDateTime

This was exactly what I was looking for. I needed to add in the date diff and summation, as well as some is null checks, but Ed's solution worked perfectly for what I asked. — Greg Maslin, Sep 28 '13 at 03:48

Summing "masked" datetime ranges using overlapping datetimes in SQL Server

1 Answers1