How to group records by hours considering start date and end date

I’m trying to group records by hours with consideration of duration. Assume there are long running processes and there is log data when process has been started and finished. I’m trying to get report by hours how many processes were running

The data looks like this

Process_name      Start                End

'A'              '2019/01/01 14:10'   '2019/01/01/ 14:55'
'B'              '2019/01/01 14:20'   '2019/01/01/ 16:30'
'C'              '2019/01/01 15:05'   '2019/01/01/ 15:10'

The result should be like this

Hour    ProcessQount
14      2
15      2
16      1

Answer

You can do it if you join a recursive cte which returns all the hours of the day to the table:

with cte as (
  select 0 as hour
  union all
  select hour + 1 
  from cte 
  where hour < 23 
)
select c.hour Hour, count(*) ProcessQount 
from cte c inner join tablename t
on c.hour between datepart(hh, t.[Start]) and datepart(hh, t.[End])
group by c.hour

See the demo.
Results:

> Hour | ProcessQount
> ---: | -----------:
>   14 |            2
>   15 |            2
>   16 |            1

If you change to a LEFT JOIN and count([Process_name]) then you get results for all the hours of the day:

> Hour | ProcessQount
.........................
>   12 |            0
>   13 |            0
>   14 |            2
>   15 |            2
>   16 |            1
>   17 |            0
>   18 |            0
.........................