SQL Server Need to calculate duration of business time not worked in previous month

jjhzyzn0 于 2023-03-11 发布在其他

关注(0)|答案(1)|浏览(138)

I need to be able to pull the amount of time within business hours an employee was off in the previous month.

EX: if an employee was off from 8am Friday thru 5pm the following Monday, the 'DurationOff' for that instance would be 18 hours (based on business hours being Mon-Fri, 8am-5pm).

The 2 bits of data I have are below & in SqlFiddle. Scripts can be provided if needed.

#1. TechTimeOff: Each instance a tech was off in previous month. The 'DurationOff' column is what I'm trying to achieve (in minutes or decimals).
| TechID | TechName | TimeOff_From | TimeOff_To | TimeOff_ID | From(MinuteOfTheMonth) | To(MinuteOfTheMonth) | *DurationOff |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | Beaver | 2023-02-01 08:00 | 2023-02-01 16:30 | 23 | 480 | 990 | 8.50 hrs (or 510 mins) |
| 2 | Wally | 2023-02-01 08:00 | 2023-02-03 17:00 | 24 | 480 | 3060 | 27.00 hrs (or 1620 mins) |
| 3 | Ward | 2023-02-01 08:00 | 2023-02-06 17:00 | 25 | 480 | 6120 | 36.00 hrs (or 2160 mins) |

#2. Calendar: Every minute in the previous month. BusinessTime = 'No' if that minute isn't within Business Hour parameters.
(only showing rows applicable to this post)

Date&Time	DayName	MinuteOfTheMonth	HourOfTheDay	BusinessTime
2023-02-01 07:59:00	Wednesday	479	7	No
2023-02-01 08:00:00	Wednesday	480	8	Yes
2023-02-01 16:30:00	Wednesday	990	16	Yes
2023-02-03 17:00:00	Friday	3060	17	Yes
2023-02-04 08:00:00	Saturday	1920	8	No
2023-02-06 17:00:00	Monday	6120	17	Yes

My plan was to add a new column ('MinuteOfTheMonth') to TechTimeOff that shows the minute of the month for every minute during the time off period. Then I could join TechTimeOff to Calendar on the MinuteOfTheMonth and filter out the minutes that aren't BusinessTime.

I just can't figure out how to break out the time off periods into rows by the minute.

So, I either need help with that, or suggestions on a better way to do this. Any help is appreciated!

sql-server

来源：https://stackoverflow.com/questions/75668248/need-to-calculate-duration-of-business-time-not-worked-in-previous-month

1条答案

按热度按时间

pgccezyw1#

Since your Calendar table contains for every minute, you can just join to it and perform a count(*) , that is the duration you required in minutes

select  t.TechID, t.TechName, Duration = count(*)
from    TechTimeOff t
        inner join Calendar c   on  c.Day_Time >= t.TimeOff_From
                                and c.Day_Time <  t.TimeOff_To
where   c.BusinessTime  = 'Yes'
group by t.TechID, t.TechName;

赞(0）回复(0）举报 2023-03-11

我来回答

SQL Server Need to calculate duration of business time not worked in previous month

1条答案

相关问题

热门标签

最新问答