time different excluding overlap period

Actually, I think this may be what you're looking for...

declare @tbl table(id int, startTime datetime, endTime datetime)

insert into @tbl values (1, '12/31/2014 09:00:00', '1/3/2015 09:00:00')
--time difference is 72 hrs
insert into @tbl values (2, '12/31/2014 10:00:00', '1/2/2015 09:00:00')
--no need to add this time difference, as this period already calculated above
insert into @tbl values (3, '1/3/2015 10:00:00', '1/3/2015 12:00:00')
--ime difference is 2 hrs

--==================================

SELECT 
        t.id,
        t.startTime,
        t.endTime,
        COALESCE(DATEDIFF(hh, 
                CASE WHEN t.startTime > LAG(t.endTime, 1) OVER (ORDER BY t.startTime) THEN t.startTime ELSE LAG(t.endTime, 1) OVER (ORDER BY t.startTime) END,
                CASE WHEN t.endTime > LAG(t.endTime, 1) OVER (ORDER BY t.startTime) THEN t.endTime ELSE LAG(t.endTime, 1) OVER (ORDER BY t.startTime) END
                ), DATEDIFF(hh, t.startTime, t.endTime)) AS Diff
FROM @tbl t
ORDER BY 
      t.ID

Uri's code relies on the fact that the start date on row 2 is exactly the same as row 1. If you alter that time in the slightest it will get thrown back into mix.

The code I posted doesn't have that dependency. It simply carves out any overlap and calculates the hours aren't apart of the overlap.

HTH,

Jason

---

Jason Long