need help calculating the hours considering the specified timings.....

Hi All,

Thank you so much for your time. Could someone please guide me through this calculation.

About what I do: We have some applications that are used by 3rd parties and they do some testing as per their testing window times. When something is wrong we get an incident ticket and iam trying to calculate like how long the application is available, how long the app was not available but as per the testing window schedule for each 3rd party

1.What is the Issue? When the incident ticket is opened and closed on the same day my calculation is right -

Scenario1

1.1  But when the incident is opened on Friday and it goes to Monday or Tuesday I should not consider saturday, sunday and i should only look at the testing window timings and calculate the impacted hours as per the testing window for each partner

Scenario2

1.2 When the Incident is Opened  say Monday and it is closed on Tuesday we calculate the total number of hours for monday using the testing window schedule and on tuesday also we should only calculate the hours using the testing window schedule only - we are only worried about the impacted hours for each partner during the testing hours window only

sample data

CREATE TABLE dbo.Sample_Data
(
[Partner Name] varchar(20),
[Incident Number] varchar(20),
[UAT Start Time] TIME,
[UAT End Time] TIME,
[Partner Window Hours] INT,
[Opened Time For Calc] DATETIME,
[End Time For Calc] DATETIME
)

INSERT INTO [dbo].[Sample_Data]
([Partner Name],[Incident Number],[UAT Start Time],[UAT End Time],[Partner Window Hours],[Opened Time For Calc],[End Time For Calc])
VALUES ('Sea World','INC998877','10:00:00','18:00:00',8,'2023-01-20 10:00:00','2023-01-20 16:16:00' )

INSERT INTO [dbo].[Sample_Data]
([Partner Name],[Incident Number],[UAT Start Time],[UAT End Time],[Partner Window Hours],[Opened Time For Calc],[End Time For Calc])
VALUES ('Disney World','INC112233','09:00:00','21:00:00',12,'2023-01-27 09:00:00','2023-01-31 16:40:00' )

INSERT INTO [dbo].[Sample_Data]
([Partner Name],[Incident Number],[UAT Start Time],[UAT End Time],[Partner Window Hours],[Opened Time For Calc],[End Time For Calc])
VALUES ('Adventure World','INC334455','10:00:00','18:00:00',8,'2023-04-10 10:00:00','2023-04-11 15:00:00' )

From the below Row 2 for the partner sea world - UAT Impacted Hours is 6.27 is correct because the ticket is opened and closed on the same day - and the impacted hours are within the UAT start time and UAT end time so the down time is 6.27 hours

For the Row 3 - Adventure world partner: The ticket is opened on April 10 2023 Monday and it is closed on April 11 2023 Tuesday at 3 PM - so the UAT Impacted hours here are April 10 2023 (Monday)

impacted hours are 8 because it was not available from 10 to 6 - 8 hours and on tuesday it is not available from 10 to 3pm so it is 5 hours so  8 hours plus 5 hours=13 hours should be UAT Impacted Hours

For the Row 1 -This is more complex because the ticket is opened on Friday and it was closed on Tuesday so my UAT Impacted Hours should be like

Friday - 12 Hours because the partner testing window is 12 hours as per uat start and end time

Saturday 0 hours  - we do not consider

Sunday 0 Hours - we do not consider 

Monday 12 Hours  - because the partner testing window is 12 hours as per uat start and end time

Tuesdsay - ticket is closed at 4.40 so the impacted hours are 9AM to 4.40 PM i.e 7 hours 40 minutes

so if we calculate 

friday 12 hours

satday 0 hours

sunday 0 hours

monday 12 hours

tues        7 hours 40 mins

             -------------------

             31 hours 40 mins 

SELECT * ,
CAST(
DATEDIFF(second,[Opened Time For Calc],[End Time For Calc])/3600.0 AS Decimal(10,2) ) AS [UAT Impacted Hours]
FROM [dbo].[Sample_Data]