Calculate hours worked between 2 dates-CodePudding

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?

CodePudding user response：

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;