I am generating a report on production. I have logged in a DB when the machine is in automatic, batch count, date_time, product and a few others.
One thing I want to do is generate an index for a for loop to get the unique batches for that day.
The issue is the batch count isn’t reliably logged, sometimes it skips a number and others it logs the same number multiple rows.
If it were reliable I would do:
select count(automatic) from table where batch_count = 1
However as it can sometimes skip 1 or else have multiple 1’s for batch count, I need to try a different strategy. Think I might need a sub query like:
select count(automatic) from table where (select batch_count where batch_count between 1 and 5 order by batch count limit 1)
Just don’t seem to be getting the syntax right… any pointers?
Which database are you using? -- MySQL in title, I cant read.
select count(automatic) from table where (select batch_count where batch_count between 1 and 5 order by batch count limit 1)
Your where clause doesn't evaluate to a boolean.
If you look at the subquery:
select batch_count where batch_count between 1 and 5 order by batch count limit 1
This will always return the lowest batch count number if it is between 1 and 5.
So, if you sub this into the original query you get this:
select count(automatic) from table where 1
Does this modified query match what you thought you were doing?
select count(automatic) from table where batch_count = (select batch_count where batch_count between 1 and 5 order by batch count limit 1)
Yes, might have to wait a while for them to get onto the 2nd batch of the day to test it
That’s what I want, select 1 if it is 1, if not select 2 if that’s the next available number. Also, if there are multiple 1’s, only count 1.
I am interested in this bit, what is your mechanism for logging this to database?
It's really hard to visualise whats in your table, are you able to show sample data?
A gateway scheduled script, running at 1s intervals, if machine bit is automatic log the data.
Might I suggest a gateway tag change script?
The tag change script will process the published change, so it should capture the change faster and more reliably than the 1s resolution you are getting right now.
They haven’t started production yet today, never mind get onto 2nd batch
This is the right syntax, I get a result back, however the logic still isn’t exactly what I want. If I run the subquery as between 4 and 5 I get a count of 6, if I run it between 3 and 9 I get a count of 9. Did some google’ing and it was suggesting that using a limit in a sub query isn’t allowed, however I think that relates to an older version of MySQL as I don’t get an explicit error. I am using 8.0.42.
