how to query the amount of time a machine was running during a shift

[BobW2961]

I have a plc connected to a machine and the plc sends a record to my access database every time the machine is switched to auto and manual.


If the machine is put into auto, then a record is created with an event number of 1 and time & date.
If the machine is put into manual, then a record is created with an event number of 2 and time & date.

Our 1st shift is 7:00am to 3:00pm

I would like a report that would show the amount of time that the machine was in auto during that shift, and the amount of time it was in manual during that shift.

I would somehow need to total all of the times between auto and manual during that time period. Keeping in mind that if the machine was already in auto at the beginning of the shift, then the time from the beginning of the shift to the first manual record would need to be counted as auto time. If the machine was in manual at the beginning of the shift, then the time from the beginning of the shift to the first auto record would need to be counted as manual time.

Any help would be appreciated.

Thanks

[CJ_London]

suggest provide some realistic example data and the outcome required from that example data as descriptions fail to be clear enough.

for example is date and time in separate fields? or one field? and if one field what order? time then date or date then time?

Keeping in mind that if the machine was already in auto at the beginning of the shift

you say Our 1st shift is 7:00am to 3:00pm - what about other shifts? do they not use the machine? if this is required for other shifts, what happens with the midnight shift?

is the machine always running from the start to the end of the shift in either event 1 or 2 mode? or can there be breaks? perhaps for servicing, mechanical/electrical breakdown, no-one available to monitor it?

And to be clear, you never get two events of the same type next to each other manual>break>manual which based on what you have described would be manual>manual

From what has been described, you only need to calculate manual, since auto would simply be length of shift minus the manual time (or the other way round)

[BobW2961]

My record is and autonumber ID field, eventnumber, time&date
I'm sure you agree the ID field is not relevant to this discussion, but the eventnumber field is an integer and the time&date field is date format long form. example 3/30/2022 3:27:01PM.
event number 1 is auto mode
event number 2 is manual mode

sample data would look like this:
1,1,3/30/2022 6:35:14 AM
2,2,3/30/2022 6:52:34 AM
3,1,3/30/2022 7:10:12 AM
4,2,3/30/2022 1:17:32 PM
5,1,3/30/2022 2:37:51 PM
6,2,3/30/2022 5:02:02 PM
7,1,3/30/2022 5:27 PM

you ask "you say Our 1st shift is 7:00am to 3:00pm - what about other shifts?"
If I am given a method of producing the results that I am looking for this shift, then I assume I will be able to use the same method to query the results for the other shifts.

you ask "is the machine always running from the start to the end of the shift in either event 1 or 2 mode?"
Yes, see sample data. At 6:52:34 AM the machine was shut down.
At 7:10:12 the machine was started back up so at the first 10 mins and 12 seconds of shift 1 was machine down time.

There are no servicing or breakdowns, only running (Auto Mode) or not running (manual mode).
If the machine is down and being worked on, then the last record in the table should be an eventnumber 2 and it should have a date/time of when it was switched from auto to manual.
the records are created anytime the machine is switched from auto to manual or from manual to auto.
If the machine was switched from manual to auto at 6:45am and ran non stop until 6:45 pm then there would be no record at all in from 7:00 am to 3:00pm.
There would be a event 1 at 6:45 am and then an event 2 at 6:45 pm. no records from 7:00am to 3:00 pm but since the last record before the shift started was a 1 then all of 1st shift was run time.

you are correct. There logically should never be two events of the same type next to each other.

Yes you are correct, if you calculate the run time on first shift then the down time should be the remainder of the 8 hour shift. Just math at that point.

[CJ_London]

sample data would look like this:

Data would indicate you only have one machine - is this the case? or do you have multiple machines which all generate this data and you store it is separate tables? or something else?

if I am given a method of producing the results that I am looking for this shift, then I assume I will be able to use the same method to query the results for the other shifts.

unlikely - shifts that cross over midnight may need a more complex solution.

And for manual, I'll read that as down time, not someone manually operating the machine

so the next question is how frequently you get this data, what do you do with it once received? store in a table or tables? what frequency of receipt and when do you need the report. The reason is because of your comment

If the machine was switched from manual to auto at 6:45am and ran non stop until 6:45 pm then there would be no record at all in from 7:00 am to 3:00pm.
There would be a event 1 at 6:45 am and then an event 2 at 6:45 pm. no records from 7:00am to 3:00 pm but since the last record before the shift started was a 1 then all of 1st shift was run time.

To determine this you need to be able to 'see' the last record prior to the shift - which might be previous shift, might be previous day or week if it was a 'good' run. Or in the case of no prior record, i.e. start of your recorded data, you would need to see the first one after the shift

you will also need a table to record your shift detail - as a minimum shiftID, start time and end time.

So if you can provide the additional information we should be able to suggest something.

[ArviLaanemets]

Our 1st shift is 7:00am to 3:00pm

I.e. shifts are starting at 7.00 AM, 3:00 PM, and 11:00 PM. So at start you have to decide, to which date 3rd shift time recordings belong. There are 3 possibilities:
1. They belong all to date when shift started (i.e. 1 hour from today 11:00 PM to midnight and 7 hours from midnight to 7:00 AM tomorrow are todays working hours);
2. They belong to date with most working hours (i.e. 1 hour from today 11:00 PM to midnight and 7 hours from midnight to 7:00 AM tomorrow working hours from tomorrow);
3. They belong to their real date (i.e. todays working hours are starting from past midnight and end with next midnight - 3rd shifts are splitted between 2 dates.

Only after you decided about 3rd shift, you can start to design the query for report. I think you have an UNION query where original your original data is splitted between quite a number of several subqueries, like

(Select auto data from 1st and 2nd shift with original date in result where start time was before end of shift and end time was after start of shift)
UNION
(Select manual data from 1st and 2nd shift with original date in result where start time was before end of shift and end time was after start of shift)
UNION
(Select auto data from 3rd shift with original date in result where start time was before end of previous 3rd shift and end time was after past midnight) //in case you decided for option 3.
Or
...