Logged Times Challenge (1 Viewer)

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 17:57
Joined
Feb 5, 2019
Messages
312
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.


WorksOrderRecordIDWorksOrderReferenceBookingDateOperationReferenceEnteredByStartTimeFinishTime
127304642​
21639-003
13/02/2024​
Production - Kit CheckOperator
13/02/2024 08:34:40​
13/02/2024 14:07:49​
127304642​
21639-003
13/02/2024​
Single Wire CutOperator
13/02/2024 08:34:48​
13/02/2024 14:07:52​
127304642​
21639-003
13/02/2024​
Single Wire StripOperator
13/02/2024 08:34:56​
13/02/2024 14:07:56​
127304642​
21639-003
13/02/2024​
Labels - PrintOperator
13/02/2024 08:35:02​
13/02/2024 14:08:00​
127304642​
21639-003
13/02/2024​
Terminal CrimpOperator
13/02/2024 08:35:07​
13/02/2024 14:08:04​
127304642​
21639-003
13/02/2024​
Contact CrimpOperator
13/02/2024 08:35:11​
13/02/2024 14:08:07​
127304642​
21639-003
13/02/2024​
Lay-up/Twist CableOperator
13/02/2024 08:35:16​
13/02/2024 14:08:12​
127304642​
21639-003
13/02/2024​
Space Tape CablesOperator
13/02/2024 08:35:22​
13/02/2024 14:08:16​
127304642​
21639-003
13/02/2024​
Fit Ty-RapsOperator
13/02/2024 08:35:26​
13/02/2024 14:08:21​
127304642​
21639-003
13/02/2024​
Fit Braid SleeveOperator
13/02/2024 08:35:30​
13/02/2024 14:08:25​
127304642​
21639-003
13/02/2024​
Load Connector ContactsOperator
13/02/2024 08:35:40​
13/02/2024 14:08:29​
127304642​
21639-003
13/02/2024​
Fit Backshell AdaptorOperator
13/02/2024 08:35:44​
13/02/2024 14:08:33​
127304642​
21639-003
13/02/2024​
Fit Bandstrap ClampOperator
13/02/2024 08:35:49​
13/02/2024 14:08:37​
127304642​
21639-003
13/02/2024​
Labels - ShrinkOperator
13/02/2024 08:35:54​
13/02/2024 14:08:41​
I hope I have explained myself correctly and this makes sense.

~Matt
 
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.
 
Last edited:
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 ??
 
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
 
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
 
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
 
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.

WorksOrderRecordIDWorksOrderReferenceBookingDateEnteredByStartTimeFinishTime
12730464221639-00312-Feb-24Operator 112/02/2024 15:38:0512/02/2024 15:39:47
12730464221639-00313-Feb-24Operator 213/02/2024 08:11:4813/02/2024 08:13:57
12730464221639-00313-Feb-24Operator 313/02/2024 08:34:4013/02/2024 14:07:49
12730464221639-00313-Feb-24Operator 313/02/2024 08:34:4813/02/2024 14:07:52
12730464221639-00313-Feb-24Operator 313/02/2024 08:34:5613/02/2024 14:07:56
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:0213/02/2024 14:08:00
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:0713/02/2024 14:08:04
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:1113/02/2024 14:08:07
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:1613/02/2024 14:08:12
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:2213/02/2024 14:08:16
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:2613/02/2024 14:08:21
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:3013/02/2024 14:08:25
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:4013/02/2024 14:08:29
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:4413/02/2024 14:08:33
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:4913/02/2024 14:08:37
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:5413/02/2024 14:08:41

And below is the query as above.




WorksOrderRecordIDWorksOrderReferenceEnteredByBookingDateTotalStartTimeTotalFinishTimeJobTime
127304642​
21639-003Operator 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.

1708006415172.png


~Matt
 
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.
 
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.

WorksOrderRecordIDWorksOrderReferenceBookingDateEnteredByStartTimeFinishTime
12730464221639-00312-Feb-24Operator 112/02/2024 15:38:0512/02/2024 15:39:47
12730464221639-00313-Feb-24Operator 213/02/2024 08:11:4813/02/2024 08:13:57
12730464221639-00313-Feb-24Operator 313/02/2024 08:34:4013/02/2024 14:07:49
12730464221639-00313-Feb-24Operator 313/02/2024 08:34:4813/02/2024 14:07:52
12730464221639-00313-Feb-24Operator 313/02/2024 08:34:5613/02/2024 14:07:56
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:0213/02/2024 14:08:00
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:0713/02/2024 14:08:04
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:1113/02/2024 14:08:07
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:1613/02/2024 14:08:12
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:2213/02/2024 14:08:16
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:2613/02/2024 14:08:21
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:3013/02/2024 14:08:25
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:4013/02/2024 14:08:29
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:4413/02/2024 14:08:33
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:4913/02/2024 14:08:37
12730464221639-00313-Feb-24Operator 313/02/2024 08:35:5413/02/2024 14:08:41

And below is the query as above.




WorksOrderRecordIDWorksOrderReferenceEnteredByBookingDateTotalStartTimeTotalFinishTimeJobTime
127304642​
21639-003Operator 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?
 
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
 

Users who are viewing this thread

Back
Top Bottom