'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 |
|---|
