Logged Times Challenge (1 Viewer)

MattBaldry · Feb 15, 2024

Hello forum brains,

I am hoping someone might have an idea on a solution here. We have a 3rd party application to for our production people to log their times on jobs. It works, almost, how we want it to. Where it falls down is when people log onto multiple operations at the same time (which is possible if operations can be done together). At the moment if someone logs onto 3 operations at the same time. And logs 6 hours, the 3rd part system logs this as 18 hours, not 3 hours for each operation.

The operator has to login via barcode, so cannot log the exact same start or end time on different operations. I am after the best way of looking for times that are close, have the same WorksOrderID and then finding the first start time, the last end time and logging just that amount of time to the WorksOrder.

If we use the below as an example, this operator was able to do all these at the same time (simple job) but their system would log these as all individual times.

This would be 14 x 5:33 (ish), but I want to be able to display this just as the the overall time.

This would need to be unique to the WorksOrderRecordID and then the EnteredBy as 2 people may be working on different operations on the same works order and I need to log the individual operator time per works order then total those times for the total spent by all people who logged onto that job.

WorksOrderRecordID	WorksOrderReference	BookingDate	OperationReference	EnteredBy	StartTime	FinishTime
127304642	21639-003	13/02/2024	Production - Kit Check	Operator	13/02/2024 08:34:40	13/02/2024 14:07:49
127304642	21639-003	13/02/2024	Single Wire Cut	Operator	13/02/2024 08:34:48	13/02/2024 14:07:52
127304642	21639-003	13/02/2024	Single Wire Strip	Operator	13/02/2024 08:34:56	13/02/2024 14:07:56
127304642	21639-003	13/02/2024	Labels - Print	Operator	13/02/2024 08:35:02	13/02/2024 14:08:00
127304642	21639-003	13/02/2024	Terminal Crimp	Operator	13/02/2024 08:35:07	13/02/2024 14:08:04
127304642	21639-003	13/02/2024	Contact Crimp	Operator	13/02/2024 08:35:11	13/02/2024 14:08:07
127304642	21639-003	13/02/2024	Lay-up/Twist Cable	Operator	13/02/2024 08:35:16	13/02/2024 14:08:12
127304642	21639-003	13/02/2024	Space Tape Cables	Operator	13/02/2024 08:35:22	13/02/2024 14:08:16
127304642	21639-003	13/02/2024	Fit Ty-Raps	Operator	13/02/2024 08:35:26	13/02/2024 14:08:21
127304642	21639-003	13/02/2024	Fit Braid Sleeve	Operator	13/02/2024 08:35:30	13/02/2024 14:08:25
127304642	21639-003	13/02/2024	Load Connector Contacts	Operator	13/02/2024 08:35:40	13/02/2024 14:08:29
127304642	21639-003	13/02/2024	Fit Backshell Adaptor	Operator	13/02/2024 08:35:44	13/02/2024 14:08:33
127304642	21639-003	13/02/2024	Fit Bandstrap Clamp	Operator	13/02/2024 08:35:49	13/02/2024 14:08:37
127304642	21639-003	13/02/2024	Labels - Shrink	Operator	13/02/2024 08:35:54	13/02/2024 14:08:41

I hope I have explained myself correctly and this makes sense.

~Matt

gemma-the-husky · Feb 15, 2024

There's an API call called gettickcount which stores a time stamp in 1000ths of a second. I use that to get a start and end time, when I want to time a process. Can you use that idea?

Elapsedtime (in seconds) = (timestamp1 - timestamp2)/1000

I actually use this idea as a permanent tracker in some apps, behind the scenes. It's then available if some process is causing a bottleneck, as I can display a time stamp history on demand.

ebs17 · Feb 15, 2024

Total time would be the StartTime and FinishTime of a period that is formed from overlapping periods, in the example
13/02/2024 08:34:40 - 13/02/2024 14:08:41 ??

MattBaldry · Feb 15, 2024

ebs17 said:
Total time would be the StartTime and FinishTime of a period that is formed from overlapping periods, in the example
13/02/2024 08:34:40 - 13/02/2024 14:08:41 ??

Yes, this is correct. The current system logs all these as different times. I want the first start, the last end and then to discount all starts and ends by the same person on the same job between the first and last ones if that makes sense.

And then use that time as the amount of time logged on that job.

~Matt

ebs17 · Feb 15, 2024

What do you think of it?

SQL:

SELECT
   A.WorksOrderRecordID,
   A.EnteredBy,
   A.BookingDate,
   COUNT(*) AS Number,
   MIN(A.StartTime) AS TotalStartTime,
   MAX(B.FinishTime) AS TotalFinishTime,
   TotalFinishTime - TotalStartTime AS JobTime
FROM
   TableX AS A
      INNER JOIN TableX AS B
      ON A.WorksOrderRecordID = B.WorksOrderRecordID
         AND
      A.EnteredBy = B.EnteredBy
         AND
      A.BookingDate = B.BookingDate
WHERE
   A.StartTime <= B.FinishTime
      AND
   A.FinishTime >= B.StartTime
      AND
   A.StartTime < B.StartTime
GROUP BY
   A.WorksOrderRecordID,
   A.EnteredBy,
   A.BookingDate

MattBaldry · Feb 15, 2024

ebs17 said:

What do you think of it?

SQL:

SELECT
   A.WorksOrderRecordID,
   A.EnteredBy,
   A.BookingDate,
   MIN(A.StartTime) AS TotalStartTime,
   MAX(B.FinishTime) AS TotalFinishTime,
   TotalFinishTime - TotalStartTime AS JobTime
FROM
   TableX AS A
      INNER JOIN TableX AS B
      ON A.WorksOrderRecordID = B.WorksOrderRecordID
         AND
      A.EnteredBy = B.EnteredBy
         AND
      A.BookingDate = B.BookingDate
WHERE
   A.StartTime <= B.FinishTime
      AND
   A.FinishTime >= B.StartTime
      AND
   A.StartTime < B.StartTime
GROUP BY
   A.WorksOrderRecordID,
   A.EnteredBy,
   A.BookingDate

This looks good, however there is an additional challenge I forgot to add. There may be times where an operator logs onto another operation, on the same day, but after the last finish time. this would then need to be treated as brand new time logged.

As an example, the same operator above may log another operation on the same day starting at 15:23:06 and ending at 16:00:00.

That was the part I forgot and is the main stumbling block I was seeing.

~Matt

MattBaldry · Feb 15, 2024

ebs17 said:

What do you think of it?

SQL:

SELECT
   A.WorksOrderRecordID,
   A.EnteredBy,
   A.BookingDate,
   COUNT(*) AS Number,
   MIN(A.StartTime) AS TotalStartTime,
   MAX(B.FinishTime) AS TotalFinishTime,
   TotalFinishTime - TotalStartTime AS JobTime
FROM
   TableX AS A
      INNER JOIN TableX AS B
      ON A.WorksOrderRecordID = B.WorksOrderRecordID
         AND
      A.EnteredBy = B.EnteredBy
         AND
      A.BookingDate = B.BookingDate
WHERE
   A.StartTime <= B.FinishTime
      AND
   A.FinishTime >= B.StartTime
      AND
   A.StartTime < B.StartTime
GROUP BY
   A.WorksOrderRecordID,
   A.EnteredBy,
   A.BookingDate

This also only show 1 person, and 1 date. If there are multiple dates, it doesn't show each day and person. Below is the full table for this job in case that helps.

WorksOrderRecordID	WorksOrderReference	BookingDate	EnteredBy	StartTime	FinishTime
127304642	21639-003	12-Feb-24	Operator 1	12/02/2024 15:38:05	12/02/2024 15:39:47
127304642	21639-003	13-Feb-24	Operator 2	13/02/2024 08:11:48	13/02/2024 08:13:57
127304642	21639-003	13-Feb-24	Operator 3	13/02/2024 08:34:40	13/02/2024 14:07:49
127304642	21639-003	13-Feb-24	Operator 3	13/02/2024 08:34:48	13/02/2024 14:07:52
127304642	21639-003	13-Feb-24	Operator 3	13/02/2024 08:34:56	13/02/2024 14:07:56
127304642	21639-003	13-Feb-24	Operator 3	13/02/2024 08:35:02	13/02/2024 14:08:00
127304642	21639-003	13-Feb-24	Operator 3	13/02/2024 08:35:07	13/02/2024 14:08:04
127304642	21639-003	13-Feb-24	Operator 3	13/02/2024 08:35:11	13/02/2024 14:08:07
127304642	21639-003	13-Feb-24	Operator 3	13/02/2024 08:35:16	13/02/2024 14:08:12
127304642	21639-003	13-Feb-24	Operator 3	13/02/2024 08:35:22	13/02/2024 14:08:16
127304642	21639-003	13-Feb-24	Operator 3	13/02/2024 08:35:26	13/02/2024 14:08:21
127304642	21639-003	13-Feb-24	Operator 3	13/02/2024 08:35:30	13/02/2024 14:08:25
127304642	21639-003	13-Feb-24	Operator 3	13/02/2024 08:35:40	13/02/2024 14:08:29
127304642	21639-003	13-Feb-24	Operator 3	13/02/2024 08:35:44	13/02/2024 14:08:33
127304642	21639-003	13-Feb-24	Operator 3	13/02/2024 08:35:49	13/02/2024 14:08:37
127304642	21639-003	13-Feb-24	Operator 3	13/02/2024 08:35:54	13/02/2024 14:08:41

And below is the query as above.

WorksOrderRecordID	WorksOrderReference	EnteredBy	BookingDate	TotalStartTime	TotalFinishTime	JobTime
127304642	21639-003	Operator 3	13/02/2024	13/02/2024 08:34:40	13/02/2024 14:08:41	0.231956018520577

The JobTime is correct (05:34:01) but it is not showing the other people. I also had to remove the COUNT(*) AS Number, because of the below message.

~Matt

ebs17 · Feb 15, 2024

I hope you understand what is being done here => Understanding is more than copying and being amazed.
The first query shown only records the overlapping times and combines them into one time period.

The expanded task is now to include the remaining records.
These should then be the remaining records from the table that are not captured by the query shown (records without time overlaps). So we subtract the overlapping records from the total using an inconsistency check.

SQL:

SELECT
   A.WorksOrderRecordID,
   A.EnteredBy,
   A.BookingDate,
   A.StartTime,
   A.FinishTime,
   A.FinishTime - A.StartTime AS JobTime
FROM
   TableX AS A
      LEFT JOIN
         (
            SELECT
               A.WorksOrderRecordID,
               A.EnteredBy,
               A.BookingDate
            FROM
               TableX AS A
                  INNER JOIN TableX AS B
                  ON A.WorksOrderRecordID = B.WorksOrderRecordID
                     AND
                  A.EnteredBy = B.EnteredBy
                     AND
                  A.BookingDate = B.BookingDate
            WHERE
               A.StartTime <= B.FinishTime
                  AND
               A.FinishTime >= B.StartTime
                  AND
               A.StartTime < B.StartTime
            GROUP BY
               A.WorksOrderRecordID,
               A.EnteredBy,
               A.BookingDate
         ) AS B
         ON A.WorksOrderRecordID = B.WorksOrderRecordID
            AND
         A.EnteredBy = B.EnteredBy
            AND
         A.BookingDate = B.BookingDate
WHERE
   B.WorksOrderRecordID IS NULL

For an overall view, the two queries would now have to be combined using UNION.

Regarding errors included: I write these queries with my fingers from the top of my head. This is more difficult than for you, who has a database with usable tables where queries just have to be clicked together using an assistant.

And of course, I can't test without a database. Possible errors are normal.

Gasman · Feb 15, 2024

MattBaldry said:
This also only show 1 person, and 1 date. If there are multiple dates, it doesn't show each day and person. Below is the full table for this job in case that helps.

WorksOrderRecordID WorksOrderReference BookingDate EnteredBy StartTime FinishTime
127304642 21639-003 12-Feb-24 Operator 1 12/02/2024 15:38:05 12/02/2024 15:39:47
127304642 21639-003 13-Feb-24 Operator 2 13/02/2024 08:11:48 13/02/2024 08:13:57
127304642 21639-003 13-Feb-24 Operator 3 13/02/2024 08:34:40 13/02/2024 14:07:49
127304642 21639-003 13-Feb-24 Operator 3 13/02/2024 08:34:48 13/02/2024 14:07:52
127304642 21639-003 13-Feb-24 Operator 3 13/02/2024 08:34:56 13/02/2024 14:07:56
127304642 21639-003 13-Feb-24 Operator 3 13/02/2024 08:35:02 13/02/2024 14:08:00
127304642 21639-003 13-Feb-24 Operator 3 13/02/2024 08:35:07 13/02/2024 14:08:04
127304642 21639-003 13-Feb-24 Operator 3 13/02/2024 08:35:11 13/02/2024 14:08:07
127304642 21639-003 13-Feb-24 Operator 3 13/02/2024 08:35:16 13/02/2024 14:08:12
127304642 21639-003 13-Feb-24 Operator 3 13/02/2024 08:35:22 13/02/2024 14:08:16
127304642 21639-003 13-Feb-24 Operator 3 13/02/2024 08:35:26 13/02/2024 14:08:21
127304642 21639-003 13-Feb-24 Operator 3 13/02/2024 08:35:30 13/02/2024 14:08:25
127304642 21639-003 13-Feb-24 Operator 3 13/02/2024 08:35:40 13/02/2024 14:08:29
127304642 21639-003 13-Feb-24 Operator 3 13/02/2024 08:35:44 13/02/2024 14:08:33
127304642 21639-003 13-Feb-24 Operator 3 13/02/2024 08:35:49 13/02/2024 14:08:37
127304642 21639-003 13-Feb-24 Operator 3 13/02/2024 08:35:54 13/02/2024 14:08:41

And below is the query as above.

WorksOrderRecordID WorksOrderReference EnteredBy BookingDate TotalStartTime TotalFinishTime JobTime
127304642
21639-003 Operator 3
13/02/2024
13/02/2024 08:34:40
13/02/2024 14:08:41
0.231956018520577

The JobTime is correct (05:34:01) but it is not showing the other people. I also had to remove the COUNT(*) AS Number, because of the below message.

View attachment 112578

~Matt

Just change the name :-(
Have you tried adding group by Operator?

MattBaldry · Feb 15, 2024

ebs17 said:
I hope you understand what is being done here => Understanding is more than copying and being amazed.
The first query shown only records the overlapping times and combines them into one time period.

The expanded task is now to include the remaining records.
These should then be the remaining records from the table that are not captured by the query shown (records without time overlaps). So we subtract the overlapping records from the total using an inconsistency check.

SQL:

SELECT A.WorksOrderRecordID, A.EnteredBy, A.BookingDate, A.StartTime, A.FinishTime, A.FinishTime - A.StartTime AS JobTime FROM TableX AS A LEFT JOIN ( SELECT A.WorksOrderRecordID, A.EnteredBy, A.BookingDate FROM TableX AS A INNER JOIN TableX AS B ON A.WorksOrderRecordID = B.WorksOrderRecordID AND A.EnteredBy = B.EnteredBy AND A.BookingDate = B.BookingDate WHERE A.StartTime <= B.FinishTime AND A.FinishTime >= B.StartTime AND A.StartTime < B.StartTime GROUP BY A.WorksOrderRecordID, A.EnteredBy, A.BookingDate ) AS B ON A.WorksOrderRecordID = B.WorksOrderRecordID AND A.EnteredBy = B.EnteredBy AND A.BookingDate = B.BookingDate WHERE B.WorksOrderRecordID IS NULL

For an overall view, the two queries would now have to be combined using UNION.

Regarding errors included: I write these queries with my fingers from the top of my head. This is more difficult than for you, who has a database with usable tables where queries just have to be clicked together using an assistant.

And of course, I can't test without a database. Possible errors are normal.

Thanks ebs17,

Apologies if it sounded like I was moaning. Your code was better than anything I could have done and is how I learn. I do read and understand the code and will make use of this for sure.

~Matt

Logged Times Challenge (1 Viewer)

MattBaldry

Self Taught, Learn from the Forums

gemma-the-husky

Super Moderator

ebs17

Well-known member

MattBaldry

Self Taught, Learn from the Forums

ebs17

Well-known member

MattBaldry

Self Taught, Learn from the Forums

MattBaldry

Self Taught, Learn from the Forums

ebs17

Well-known member

Gasman

Enthusiastic Amateur

MattBaldry

Self Taught, Learn from the Forums

Similar threads

Users who are viewing this thread