Create daily logs based on records that contain multi day values

This is an extension to this question: Break down long multi day periods of time into several single day periods where the problem was solved using Tallies.

The situation I am dealing now is let’s say I have a table like this:

OutageID     ServerOutageStartDateTime      OutageLength (hrs)
1            2020/07/05 17:00               12
2            2020/07/15 11:00               3
3            2020/07/15 19:00               2
4            2020/07/22 14:00               72

The goal is to create a daily outage report over a period of time (Let’s say the Month of July) that looks like this:

Date                OutageLength (hrs)
2020/07/01               0
2020/07/02               0
2020/07/03               0
2020/07/04               0
2020/07/05               7
2020/07/06               5
2020/07/07               0
...
2020/07/15               5
...
2020/07/22               10
2020/07/23               24
2020/07/24               24
2020/07/22               14
2020/07/23               0
...

Thanks in advance.

P.S I just saw this other question: Convert Date Range to Individual Days

I am going to review it but in the meantime, please feel free to help!

Answer

This solution also uses a tally table. In this case it uses a tally TVF called fnTally.

Tally TVF (without the embedded notes (see link))

CREATE FUNCTION [dbo].[fnTally]
        (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  H2(N) AS ( SELECT 1 
               FROM (VALUES
                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    )V(N))            --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
            SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
            SELECT TOP(@MaxN)
                   N = ROW_NUMBER() OVER (ORDER BY N)
              FROM H8
;
GO

Data

drop table if exists #tTEST;
go
select * INTO #tTEST from (values 
(1, cast('2020/07/05 17:00' as datetime), 12),
(2, '2020/07/15 11:00', 3),
(3, '2020/07/15 19:00', 2),
(4, '2020/07/22 14:00', 72)) V(OutageID, ServerOutageStartDateTime, OutageLength);

Query

declare @month_dt       datetime='2020-07-01';

;with
month_hrs_cte as (
    select dateadd(hour, f.N, @month_dt) CalDtm
    from fnTally(0, datediff(d, @month_dt, eomonth(@month_dt))*24) f),
outage_hrs_cte as (
    select OutageID, dateadd(hh, f.N, o.ServerOutageStartDateTime) CalDtm
    from #tTEST o
         cross apply
         fnTally(0, o.OutageLength) f)
select convert(date, mhc.CalDtm) CalDt, count(distinct ohc.CalDtm) OutageLength
from month_hrs_cte mhc
     left join
     outage_hrs_cte ohc on mhc.CalDtm=ohc.CalDtm
group by convert(date, mhc.CalDtm);

Results

CalDt   OutageLength
2020-07-01  0
2020-07-02  0
2020-07-03  0
2020-07-04  0
2020-07-05  7
2020-07-06  6
2020-07-07  0
2020-07-08  0
2020-07-09  0
2020-07-10  0
2020-07-11  0
2020-07-12  0
2020-07-13  0
2020-07-14  0
2020-07-15  7
2020-07-16  0
2020-07-17  0
2020-07-18  0
2020-07-19  0
2020-07-20  0
2020-07-21  0
2020-07-22  10
2020-07-23  24
2020-07-24  24
2020-07-25  15
2020-07-26  0
2020-07-27  0
2020-07-28  0
2020-07-29  0
2020-07-30  0
2020-07-31  0