Hi Team,
I have the below raw data - Incident number, its open time and its resolved time. I need to calculate the unavailability of a device linked to incident based on few conditions as mentioned below.
| Device |
Incident |
Open date |
Closed date |
| Server |
INC001 |
12/7/2022 12:45:00 PM |
12/8/2022 2:50:00 PM |
| Server |
INC002 |
12/8/2022 12:45:00 PM |
12/13/2022 9:45:00 AM |
Conditions:
> Business hours to be between 7 AM to 7 PM
> Weekends to be excluded
Desired Output:
| Device |
Incident |
Open date |
Closed date |
Output to achieve - |
Details |
| Server |
INC001 |
12/7/2022 12:45:00 PM |
12/8/2022 2:50:00 PM |
14:05 hours |
12/7/2022 12:45:00 PM ---> 6:15 hours (12:45PM to 7:00 PM (Business hours))
12/8/2022 2:50:00 PM ---> 7:50 hours (7:00AM(Business hours) to 2:50PM)
Total : 14:05 hours (6:15 + 7:50) |
| Server |
INC002 |
12/8/2022 12:45:00 PM |
12/13/2022 9:45:00 AM |
33 hours |
12/8/2022 12:45:00 PM ---> 6:15 hours ( 12:45PM to 7:00 PM (Business hour))
12/9/2022 ---> 12 hours (Fullday- 7AM to 7PM business hours)
12/10/2022 ---> 0 (weekend)
12/11/2022 ---> 0 (weekend)
12/12/2022 ---> 12 hours (Fullday- 7AM to 7PM business hours)
12/13/2022 9:45:00 AM ---> 2:45 hours ( 7AM(Business hour) to 9:45AM)
Total : 33 hours (6:15 + 12 + 0 + 0 + 12 + 2:45) |
This will tell me that the device Server was unavailable for 47 minutes for that month.
Would appreciate any help on this 🙂
Regards,
Hitha Dhani
