Calculating Efficiencies with hourly DB logging

Archived Topics
1

I am trying to calculate the efficiency of our automated machines and log an hourly snapshot of those efficiencies.

The equation would be total parts run / (Run Rate * current run time). I have PLC tags associated with the total parts run. I want to be able to set my shift start and end times and include breaks – ie. lunch is 20 min and is taken between 12 and 1 so that hour only has 40 min of possible run time. All of our run rates are in minutes and will be integers.

This is a big one for us and will be utilized across the factory. We will also be using it to bounce off of to track downtime issues. I appreciate any help you could provide.

Thank you,

Keith

2

Hello Keith-

First off, good question. I think there are a number of ways this can be accomplished, so I just wanted to clarify a bit. The main big this is: do you want to record an identifier for the shift? That is, do you need to be able to create a shift schedule (that could be modified through the db/pmi), and track the results for those shifts… or do you just want to update the value every hour, and make sure to not included time that it shouldn’t be running?

Is the “current run time” stored by the plc, or is it going to be calculated based on the shifts- the amount of time it “should have been” running. When will these values be cleared? Every day, or every actual shift change?

Regards,

3

Great questions.

I would like an identifier for the shifts. We may eventually want to look at historical data by shift so I might as well differentiate that now. In addition, I want to take a snapshot each hour on the hour so that I can further break down the historical data. I still want to be able to exclude break times as running time from any particular hour as needed.

The “current run time” is based on the amount of time the machine “should have been running.” This will need to be calculated in the db as the PLCs are not going to give me that data. I am evaluating the efficiency of the machine itself and not the manufacturing process. Values will be reset at the end of each shift.

I would like to have control over shift schedule: start time, end time, breaks – if possible. This offers me the highest level of flexibility for future developement.

Thank you again for all of your help.

Keith

4

Hi Keith-

So, in tossing the idea around, we came up with another distinction: Do you want to be able to have a more “real-time” value that gets updated every few seconds (and logged every hour), or is it sufficient to just calculate the effeciency at the end of the hour?

The general situation is this: It’s easy to create a table of shifts, with start times and end times, and to find out what shift you’re currently in. My original idea was to give each shift a numerical ID, and then make breaks just shifts with ID 0 or something. Then, later on, you could choose to select just the shifts that weren’t breaks. I think this would be fine for logging when a shift changes, but may not be exactly what you want.

Another option is to have a list of shifts and a list of breaks. At any given time, you need to calculate how much the machine should have been running, so you need to select (NOW-Shift Start)-{any break time up til now}. I think this is possible, it just might not be the prettiest query.

Finally, the easiest way, would probably be to have a table of hours, with how many minutes of break there are in that hour. Then, at the end of the hour, you could do your calculation, and log it. This could be done in combination with a shift table, but I guess that means the shifts need to start on the hour.

I’m more or less just thinking out loud here, let me know what you think about these options and we can go from there.

Regards,

5

I am really looking for all the bells and whistles here.

I want real time (sec by sec) updated data that I can display PMI and I want to log a snapshot at the end of the hour (or very close) for historical queries.

I am thinking a shift ID ( 1, 2, 3) and 30 minute designators ( 0000, 0030, 0100, 0130, 0200, 0230,. . . ., 1200, 1230, 1300, 1330,. . ., 2300, 2330). This would allow me the option to use shift start times that do not fall on the hour. I have 2 types of breaks - 10 min & 20 min. If I could have control over which hour I apply the break multiplier to, which hour represents shift start and end, and which run rate applies, that should give me what I need. They could each be placed in their own tables that I could maintain through FactoryPMI.

I know this is a tall order and I appreciate the help. I hope the above info helps.

Thank you,

Keith

6

OK, Sooo… here’s what I have so far:

The first task is to calculate the time you should have been running, in this shift, up til now.

My idea was to set up 2 tables, pretty much identical:
Shifts

id - integer
starttime - time
endtime - time

Breaks

id - integer
starttime - time
endtime - time

NOTE: Let me say this now- everything I’m posting is built around MySQL. If you’re not using mysql… the data types, functions, etc probably won’t be the same.

Ok, so the query I came up with to get the current amount of run time is:

SELECT
(time_to_sec(current_time-s.starttime)-time_to_sec(
	(
	SELECT CASE WHEN count(*)>0 THEN b.endtime-b.starttime ELSE 0 END FROM breaks b WHERE b.starttime>s.starttime and b.starttime<current_time and b.endtime<current_time
	)+(
	SELECT CASE WHEN count(*)>0 THEN current_time-b.starttime ELSE 0 END from breaks b WHERE b.starttime<current_time and b.endtime>current_time
	)
))/60 FROM shifts s WHERE current_time>s.starttime and current_time<s.endtime;

I’ll explain a bit:
To get the current run time, you need 1) NOW-Shift.Startime. Then you need to remove 2) the breaks that have completed, and 3) the point up til now in a break that you might be in. My numbering 1, 2, & 3 corrospond more or less to each SELECT you see up there (though the first select actually does a bit more, adding the subqueries and all).

So, if you study the query carefully, hopefully you can see how this is happening. The query is a little “noisy”, cause I threw in the CASE statements. Their function is to provide a fallback value. That is, should you be in a position where there were no breaks already complete, the first sub query would normally return no rows. This won’t work in the calculation, so we have to qualify it by saying “if there rows returned (count=0), return ‘0’”. Also, you can see at the end that I’m dividing by 60 to get it into minutes… you could also choose to store it however you want and convert it later.

OK, so now that we have the current run time, you can calculate the efficency on the fly. There’s a few ways to log the history… the “realtime” group could update the last row of a table (and you could select that last row in PMI for status), and then another group would simply insert a row every hour, or you could update one row in a table and then use sql to store it to a history table every hour (INSERT INTO history SELECT * FROM status).

Anyhow, let me know what you think of all this, and we can go from there.

Regards,

7

Oh, one small thing I noticed about that query is that if you’re not currently in a shift (which probaby shouldn’t happen anyway), it will return null. You can get around this by wrapping everything in the top level SELECT in a COALESCE function (which returns the first non-null argument).
So, it would be like:

SELECT coalesce((time_to_sec(.......))/60, 0) FROM...
8

Now the real time efficiency is running perfectly – THANK YOU!

I need to be able to calculate and log totals, efficiency, and yield for each individual hour; not as running totals for the entire shift. I would like to do this in addition to what we already have configured.

9

Hey Keith-

So, the main goal of this is to calculate the efficiency for just that hour, instead of logging the running total… which to do, we need just the parts made in that hour (is that what you’re refering to as the “yield”?)

I guess you could do 1 of 2 things to get this number: either look up the last hour’s total and subtract it from the current, or create a seperate plc register that is like the “# of parts made” but can be cleared out after the group executes (can be done easily with the group’s “handshake” option). After this, you just need to get the runtime in the hour (1 hour - breaks in that time), which is very similar to what you’ve already done.

Let me know what you think,

10

My preference would be to query the previously stored value and subtract that from the current value. That would work except for the 1st hour of a shift.

I thought the time calculation would be similar to what I already have, but I am having trouble manipulating that code correctly.

Appreciate the help.

11

I forget- Does the total parts count reset to 0 after every shift? It must since I don’t believe you’re doing any part count lookup when the shift starts so…
Would the logic be correct to simply look up the last count, and then use an action item expression like if(LastCount>CurrentCount, CurrentCount, CurrentCount-LastCount), which should account for the first shift after the count has been reset?

As for the time, I think we can make the query pretty simple if we assume this is run at the end of the hour, and no breaks span the hour change. We can just select 60-(breaks completed):
[i]

SELECT 60-(CASE WHEN count(*)>0 THEN time_to_sec(b.endtime-b.startime)/60 ELSE 0 END) FROM breaks b WHERE b.startime>addtime(current_time, '-1:00:00') and b.startime<current_time;

There’s some room for improvement in that (such as selecting the current minute instead of using 60), but it should be a starting point. Let me know what you think.

Oh, and sorry about the delay… I wrote this earlier but never hit submit! :angry:

12

Thank you again, Colby.

I just ran it and it worked GREAT!

Keith