Extracting Hourly Impact from downtime timestamps of network nodes

[Shailesh2thakur]

I have a network nodes downtime log in the following format. The overall table (e.g. outagelog) is having 3K+ event. The SiteId may repeat. The count of Unique SiteID is 500+.

I want to extract an hourly impact( in minutes)  for each of the  siteID individually for each of the 24 hour time window in a new table. For instance, in the new table, XA2 will be have 24 rows for each 00:00 , 01:00, ....06:00, .... till 24:00 hrs window and the outage minutes will be non zero for XA2 i.e. from 00Hrs till 07Hrs . For other rows the minutes will be ZERO.  Pls help and suggest how to create this new table

Site ID	OutageStart	OutageEnd
XA2	05-02-2024 00:10	05-02-2024 06:41
XB2	05-02-2024 00:11	05-02-2024 06:23
XB3	05-02-2024 00:12	05-02-2024 00:14
YB3	05-02-2024 00:12	05-02-2024 00:15
XA1	05-02-2024 00:12	05-02-2024 00:15
XB2	05-02-2024 00:12	05-02-2024 04:28
XC1	05-02-2024 00:12	05-02-2024 00:15
XA9	05-02-2024 00:12	05-02-2024 00:13
XA1	05-02-2024 13:10	05-02-2024 00:14
XB1	05-02-2024 13:10	05-02-2024 18:41

The resulting table should be like :

Site ID	Hour Window	Minute
XA1	01:00	15
XA1	02:00	0
XA1	03:00	45
XA1	04:00	0
XA1	05:00	0
XA1	06:00	0
XA1	07:00	0
XA1	08:00	32
XA1	09:00	0

[lbendlin]

Please provide sample data that fully covers your issue. Including multiple days.  Please indicate if 05-02 is Feb 5 or May 2.
Please show the expected outcome based on the sample data you provided.

[Shailesh2thakur]

Hi Ibendin !

Thanks for response ..

My Query included two sample tables, First table is the available outage log of the network nodes. While the second table is the required output. 

and the dates inculded are for Current month i.e. Feb 

[lbendlin]

Please make your sample data more realistic. Include an outage that spans multiple days.

[Shailesh2thakur]

In fact the actual data will span across multiple dates/weeks. Since the hourly downtime timeline will be used to analyze  in energy consumption ( Disel Generator Run Hour, Electricity and Battery Bank run hours) to arrive at root casue for outage and peielfrage, if any. Energy hour line data is already available. So finaly Energy consuption pattern and outage pattern will be plotted on same hourly time winow after using slicer for a DAY. 

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.