Oracle SQL – count number of active/open tickets per hour by day

I have a dataset from oracle db that looks something like this:

ticket_num start_date          repair_date
1          1/1/2021 02:05:15   1/4/2021 09:30:00
2          1/2/2021 12:15:45   1/2/2021 14:03:00
3          1/2/2021 12:20:00   1/2/2021 13:54:00

I need to calculate the number of active tickets in an hour time slot. So if the ticket was opened before that hour, and closed after the hour it would be counted. All days and hours need to be represented regardless if there are active tickets open during that time. The expected output is:

month    day    hour   #active_tix
1        1      2      1
1        1      3      1
...
1        2      12     3
1        2      13     3
1        2      14     2
1        2      15     1
...
1        4      9      1
1        4      10     0

Any help would be greatly appreciated.

Answer

You need a calendar table. In the query below it is created on the fly

select c.hstart, count(t.ticket_num) n
from (
    -- create calendar on the fly
    select timestamp '2021-01-01 00:00:00' + NUMTODSINTERVAL(level-1, 'hour') hstart
    from dual
    connect by timestamp '2021-01-01 00:00:00' + NUMTODSINTERVAL(level-1, 'hour') < timestamp '2022-01-01 00:00:00'
) c
left join mytable t on t.start_date < c.hstart and t.repair_date >= c.hstart
group by c.hstart
order by c.hstart