By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with data as
(
select cast('2011-12-18 16:00:00' as datetime) startt , cast('2011-12-18 17:00:00' as datetime) endt
union all
select '2011-12-19 08:00:00', '2011-12-19 10:00:00'
union all
select '2011-12-19 11:00:00', '2011-12-19 13:00:00'
union all
select '2011-12-19 12:00:00', '2011-12-19 14:00:00'
union all
select '2011-12-19 13:00:00', '2011-12-19 15:00:00'
union all
select '2011-12-19 13:00:00', '2011-12-19 14:00:00'
union all
select '2011-12-20 13:00:00', '2011-12-20 14:00:00'
)
select startDt, endDt, count(*)
from
(
select lag(dt) over (order by dt) startDt, dt endDt
from
(
select startt dt from data
union
select endt dt from data
) t
) t
join data on t.startDt < data.endt and t.endDt > data.startt
group by startDt, endDt
| startDt | endDt | count(*) |
|---|---|---|
| 2011-12-18 16:00:00 | 2011-12-18 17:00:00 | 1 |
| 2011-12-19 08:00:00 | 2011-12-19 10:00:00 | 1 |
| 2011-12-19 11:00:00 | 2011-12-19 12:00:00 | 1 |
| 2011-12-19 12:00:00 | 2011-12-19 13:00:00 | 2 |
| 2011-12-19 13:00:00 | 2011-12-19 14:00:00 | 3 |
| 2011-12-19 14:00:00 | 2011-12-19 15:00:00 | 1 |
| 2011-12-20 13:00:00 | 2011-12-20 14:00:00 | 1 |