计算工作时间在2个日期之间

发布于 2025-02-09 04:35:07 字数 953 浏览 2 评论 0原文

我有一个预订桌，我需要计算工作人员一周内工作的总小时，但是我需要在午夜至午夜的下一个星期一之间切断日期。

create table Bookings (ID int IDENTITY(1,1) not null, start datetime, finish datetime, staffId int)

insert into Bookings (start, finish, staffId) values ('2022-06-19 21:00:00', '2022-06-20 07:00:00', 1)
insert into Bookings (start, finish, staffId) values ('2022-06-24 21:00:00', '2022-06-25 07:00:00', 1)
insert into Bookings (start, finish, staffId) values ('2022-06-25 21:00:00', '2022-06-26 07:00:00', 1)
insert into Bookings (start, finish, staffId) values ('2022-06-26 21:00:00', '2022-06-27 07:00:00', 1)

select *, datediff(MINUTE, start, finish)/60.0
from Bookings
where staffid = 1 and start between '2022-06-19' and '2022-06-27'

“在此处输入映像”

我需要第1行才能在2022-06-06-20 00:00开始，第4行才能在2022年完成-06-27 00：00因此，第1行的小时为7，而第4行的时间为3，因此总计30小时而不是40小时。

关于如何执行此操作的任何想法？

原文

I have a Bookings table and I need to calculate the total hours a staff member has worked in a week, but I need it to cut off dates between Monday midnight and the following Monday at midnight.

create table Bookings (ID int IDENTITY(1,1) not null, start datetime, finish datetime, staffId int)

insert into Bookings (start, finish, staffId) values ('2022-06-19 21:00:00', '2022-06-20 07:00:00', 1)
insert into Bookings (start, finish, staffId) values ('2022-06-24 21:00:00', '2022-06-25 07:00:00', 1)
insert into Bookings (start, finish, staffId) values ('2022-06-25 21:00:00', '2022-06-26 07:00:00', 1)
insert into Bookings (start, finish, staffId) values ('2022-06-26 21:00:00', '2022-06-27 07:00:00', 1)

select *, datediff(MINUTE, start, finish)/60.0
from Bookings
where staffid = 1 and start between '2022-06-19' and '2022-06-27'

enter image description here

I need row 1 to start at 2022-06-20 00:00 and row 4 to finish at 2022-06-27 00:00 so the hours for row 1 would be 7, and for row 4 would be 3, thus totaling 30 hours instead of 40.

Any ideas on how to do this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

沉默的熊 2025-02-16 04:35:07

我怀疑您需要在这里做什么，首先要更改在其中查看完成时间 >启动时间您的完成值。然后，在工作时间内，您需要使用case表达式返回列或输入参数值，这取决于较小/更大：

DECLARE @Start date = '20220620',
        @Finish date = '20220627';

SELECT ID,
       staffId,
       finish,
       staffId,
       DATEDIFF(MINUTE,CASE WHEN start < @Start THEN @Start ELSE start END,CASE WHEN finish > @Finish THEN @Finish ELSE finish END) / 60. AS Hours
FROM dbo.Bookings
WHERE staffId = 1
  AND finish > @Start
  AND start < @Finish;

I suspect what you need to do here is firstly change your WHERE to look at the finish time being after your start value, and the start time being before your finish value. Then for the hours worked, you need to use a CASE expression to return the column or input parameters value, depending which is lesser/greater:

DECLARE @Start date = '20220620',
        @Finish date = '20220627';

SELECT ID,
       staffId,
       finish,
       staffId,
       DATEDIFF(MINUTE,CASE WHEN start < @Start THEN @Start ELSE start END,CASE WHEN finish > @Finish THEN @Finish ELSE finish END) / 60. AS Hours
FROM dbo.Bookings
WHERE staffId = 1
  AND finish > @Start
  AND start < @Finish;

回复收藏 0 原文

~没有更多了~