Start and End Times with Overlap

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


BookingDateEnteredByStartTimeFinishTimeTimeLogged
31/10/2024Employee One07:19:5010:51:5203:32:02
31/10/2024Employee One07:20:0910:51:5703:31:48
31/10/2024Employee One07:20:2810:52:0003:31:32
31/10/2024Employee One07:20:4610:52:0303:31:17
31/10/2024Employee One10:52:1316:00:0305:07:50
31/10/2024Employee One10:52:2612:54:2802:02:02
31/10/2024Employee One12:54:4016:00:0303:05:23
31/10/2024Employee One12:54:5616:00:0303:05:07
31/10/2024Employee One12:55:0916:00:0303: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);
 
the correct answer is 08:40:13, why do think the extra 10 seconds is down to 10:52:03 off and 10:52:13 on? Why does 12:54:28 off and 12:54:40 not count?

Show how you are calculating your 08:40:03 value

Your sql is valid but could be simpler

Code:
SELECT
  EnteredBy,
  BookingDate,
  Min(StartTime) AS TotalStartTime,
  Max(FinishTime) AS TotalFinishTime,
  Min(StartTime)-Max(FinishTime) AS JobTime
FROM tblWorksOrderBookingTimes
WHERE BookingDate=#10/31/2024#
GROUP BY EnteredBy,BookingDate
ORDER BY BookingDate, Min(StartTime)
 
Last edited:
the correct answer is 08:40:13, why do think the extra 10 seconds is down to 10:52:03 off and 10:52:13 on? Why does 12:54:28 off and 12:54:40 not count?

Show how you are calculating your 08:40:03 value

Your sql is valid but could be simpler

Code:
SELECT
  EnteredBy,
  BookingDate,
  Min(StartTime) AS TotalStartTime,
  Max(FinishTime) AS TotalFinishTime,
  Min(StartTime)-Max(FinishTime) AS JobTime
FROM tblWorksOrderBookingTimes
WHERE BookingDate=#10/31/2024#
GROUP BY EnteredBy,.BookingDate
ORDER BY.BookingDate, Min(StartTime)
Those 10 seconds are not logged because they logged off a job at 10:52:03 and then logged onto a new job at 10:52:13. I know on this example it is only 10 seconds, but other people have greater time between a log off and log on.

If we take the below example of a different employee, you will see what I mean.

BookingDateEnteredByStartTimeFinishTimeLoggedTime
31/10/2024Employee Two07:29:0107:56:2500:27:24
31/10/2024Employee Two08:07:3214:39:4006:32:08
31/10/2024Employee Two15:10:3116:00:0300:49:32
Now I make this 07:49:04 working time, but my query is working this out to be 08:31:02 which is just the difference between the first start time and last end time.

~Matt
 
Last edited:
using VBA, you can accomplish avoiding extra secs.
 
Those 10 seconds are not logged because they logged off a job at 10:52:03 and then logged onto a new job at 10:52:13. I know on this example it is only 10 seconds, but other people have greater time between a log off and log on.

If we take the below example of a different employee, you will see what I mean.

BookingDateEnteredByStartTimeFinishTimeLoggedTime
31/10/2024Employee Two07:29:0107:56:2500:27:27
31/10/2024Employee Two08:07:3214:39:4006:32:08
31/10/2024Employee Two15:10:3116:00:0300:49:32
Now I make this 07:49:04 working time, but my query is working this out to be 08:31:02 which is just the difference between the first start time and last end time.

~Matt
Well the seconds do not even add up to your value with what you have? Your first entry is also incorrect, I get 00:27:24 ?
 
Well the seconds do not even add up to your value with what you have? Your first entry is also incorrect, I get 00:27:24 ?
I was lazy and used Excel and didn't check the workings out. You are correct, I will do them properly :)
 
If you are linking with WorksOrderRecordID why can't you just subtract start time from end time?
 
If you are linking with WorksOrderRecordID why can't you just subtract start time from end time?
The issue is they may have logged onto different worksorders at the same time, so I need to use the earliest start time and latest end time within the same time period. The 4 below are all different orders worked on at the same time.

31/10/2024Employee One07:19:5010:51:5203:32:02
31/10/2024Employee One07:20:0910:51:5703:31:48
31/10/2024Employee One07:20:2810:52:0003:31:32
31/10/2024Employee One07:20:4610:52:0303:31:17

So I need it to only show the time from 07:19:50 to 10:53:03. For this exercise I am not worried what they are working on, only the time they are working. The software we are using to log the times shows this as all these times added together, but in reality they only worked for those 3 hours 30 ish. But their software would show this as 14 hours or so worked.

~Matt
 
Wow, that is a mess. :(
Much worse than your first example. At least that had sequential entries.
The logic of their system evades me. :(
 
it evades me as well - post #9 - where do you get 10:53:09 from?



Seem to me you need to define a work period of 1 second and from the initial start time to final end time, second by second see if the employee was working on one or more jobs. If they were that is 1 second, if not it is 0 second.

If that principle is what you mean, a clumsy way to do it is create a table with 86400 records to cover a 24 hour period

then for each of those - see if the employee was working on one or more items.

I can probably mock something up for you but don't have the time right now

OK, found a bit of time and this is the sql
Code:
SELECT P.BookingDate, P.EnteredBy, CDate(Count([ctr])/86400) AS WorkTime
FROM (SELECT DISTINCT tblWorksOrderBookingTimes.BookingDate, tblWorksOrderBookingTimes.EnteredBy, usysCtr.ctr
FROM tblWorksOrderBookingTimes, sysCtr
WHERE sysCtr.ctr Between [startTime]*86400 And [FinishTime]*86400)  AS P
GROUP BY P.BookingDate, P.EnteredBy;

1731026056700.png

It's not 100 percent accurate due to the nature of decimal numbers, but you might be able to refine it further if required - based on the data in post#1 it returns 08:40:05. Because of the granularity of 1 second it is not particularly fast (took about 3 seconds on this small dataset) and produces a working recordset of around 109000 records - that is for just one person for one day


If you want a lower level of granularity changes to 86400 in all places to 1440 for minutes (these represent the number of seconds and minutes in a day) so your recordset would be reduced to around 1750 records - but not much faster performance on this dataset - a larger one maybe.

you also need another table and query to provide the counter feature

The table is called sysCounter and contains a single field of numbers 0-9
1731026096714.png



and the query (I call sysCtr) sql is:
Code:
SELECT ([singles].[num]+([tens].[num]*10)+([hundreds].[num]*100)+([thousands].[num]*1000)+([tenthousands].[num]*10000)) AS ctr
FROM sysCounter AS singles, sysCounter AS tens, sysCounter AS hundreds, sysCounter AS thousands, sysCounter AS tenthousands;
Which generates 100,000 numbers
 
Last edited:
This would be useful, but I have no idea were I would start on this.
see this demo.
Open query1 (also in design view).
view the code on Module1.

"TotalTime" column on the query is String, so you need to Convert it to date (CDate()) for
it to be useful in any calculation.
 

Attachments

Users who are viewing this thread

Back
Top Bottom