'Create a running total in SQL with hours but only using work hours

This might be a strange question... but will try to explain the best i can ...

BTW : There is no chance in implementing through Stored Procedures... it should be made in SQL Query only ... But if the only option is SP, then i have to adapt to that ...

I have a table with the following elements :

RUN WORKORDER LOCATION TRAVELTIME NUMEQUIP TOT_TIME
NO99 1 Start
NO99 2 Customer 1 112 1 8
NO99 3 Customer 2 18 11 88
NO99 4 Customer 3 22 93 744
NO99 5 Customer 4 34 3 24

I need to add a running DATE and HOUR by calculating the amount of time it takes from one line tho another BUT, and this is important, to take into consideration working hours ( from 9:00 to 13:00 and from 14:00 to 18:00 ( in US format : from 9am to 1pm, and 2pm to 6pm)... As example ... considering that my start date and time would be 10/May/2022 9:00 :

RUN WORKORDER LOCATION TRAVELTIME NUMEQUIP TOT_TIME DATE TIME
NO99 1 Start 10/05/22 9:00
NO99 2 Customer 1 112 1 8 10/05/22 10:52
NO99 3 Customer 2 18 11 88 10/05/22 11:18
NO99 4 Customer 3 22 93 744 10/05/22 14:08
NO99 5 Customer 4 34 3 24 12/05/22 10:06

This result is achieved by calculating the estimated time to make the trip between customers (TRAVELTIME), and after arriving is also added the time spent on maintenance (TOT_TIME that is Number of Equipments (NUMEQUIP) vs 8 minutes per equipment)... By this, and since customer 3 will have 744 minutes (12 h and 58 minutes) in maintenance... and those minutes will spawn through 3 days, the result should be as shown...

With the following query i can have almost the desired effect... but cannot take into account only work hours ... and all time is continuous...

Select 
     RUN,WORKORDER,LOCATION,TRAVELTIME,
     DateAdd(mi,temprunningtime-TOT_TIME,'9:00') As TIME,
     NUMEQUIP,NUMEQUIP*8 AS TOT_TIME,sum(MYTABLE.TRAVELTIME + 
     MYTABLE.TOT_TIME) OVER (ORDER BY MYTABLE.ORDER) AS temprunningtime 
FROM   MYTABLE

With this query (slightly altered) i get an running TIME, but does not take into account the 13:00-14:00 stop, and the 18:00-9:00 stop...

It might be a bit confusing but any ideias on this would be very appreciated... and i will try to explain anyway i can...



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source