MattBaldry
Self Taught, Learn from the Forums
- Local time
- Today, 04:34
- Joined
- Feb 5, 2019
- Messages
- 330
Hi all,
I have the below table and I want to work out how many hours worked in a day based on the earliest start time and latest end time a person has logged on.
In the data below we can see that Employee One logged on to 4 jobs around the same time, starting at 07:19:50 and ending these at 10:53:03 logging 03:32:13.
The next session is from 10:52:13 until 16:00:03 between 5 jobs for another 05:07:50 for a total in the day of 08:40:03
I have the below SQL which is almost working but showing this as 08:40:13 which is the extra 10 seconds between the 10:52:03 off and 10:52:13 on.
Can anyone see where I have gone wrong, or missed something?
I have the below table and I want to work out how many hours worked in a day based on the earliest start time and latest end time a person has logged on.
In the data below we can see that Employee One logged on to 4 jobs around the same time, starting at 07:19:50 and ending these at 10:53:03 logging 03:32:13.
The next session is from 10:52:13 until 16:00:03 between 5 jobs for another 05:07:50 for a total in the day of 08:40:03
BookingDate | EnteredBy | StartTime | FinishTime | TimeLogged |
31/10/2024 | Employee One | 07:19:50 | 10:51:52 | 03:32:02 |
31/10/2024 | Employee One | 07:20:09 | 10:51:57 | 03:31:48 |
31/10/2024 | Employee One | 07:20:28 | 10:52:00 | 03:31:32 |
31/10/2024 | Employee One | 07:20:46 | 10:52:03 | 03:31:17 |
31/10/2024 | Employee One | 10:52:13 | 16:00:03 | 05:07:50 |
31/10/2024 | Employee One | 10:52:26 | 12:54:28 | 02:02:02 |
31/10/2024 | Employee One | 12:54:40 | 16:00:03 | 03:05:23 |
31/10/2024 | Employee One | 12:54:56 | 16:00:03 | 03:05:07 |
31/10/2024 | Employee One | 12:55:09 | 16:00:03 | 03:04:54 |
I have the below SQL which is almost working but showing this as 08:40:13 which is the extra 10 seconds between the 10:52:03 off and 10:52:13 on.
Can anyone see where I have gone wrong, or missed something?
SQL:
SELECT A.EnteredBy, A.BookingDate, Min(A.StartTime) AS TotalStartTime, Max(B.FinishTime) AS TotalFinishTime, TotalFinishTime-TotalStartTime AS JobTime
FROM tblWorksOrderBookingTimes AS A INNER JOIN tblWorksOrderBookingTimes AS B ON (A.WorksOrderRecordID = B.WorksOrderRecordID) AND (A.EnteredBy = B.EnteredBy) AND (A.BookingDate = B.BookingDate)
GROUP BY A.EnteredBy, A.BookingDate
HAVING (((A.BookingDate)=#10/31/2024#))
ORDER BY A.BookingDate, Min(A.StartTime);