I have a flow meter, and I need to calculate its monthly consumption. It would be simple if I could just subtract the final date value from the initial date value. However, the meter resets to zero after reaching the value of 9999900. I would need to take the initial value whenever it resets, as every time it reaches zero, the minimum will always be zero. I'm having trouble figuring out how to make this calculation the way I want.
If anyone can help me with any ideas, I would appreciate it.
So, this type of operation is often called "odometer" or "hourmeter" mode. You need to know the true rollover value, which is the true total from zero to the next zero. With that, some procedures:
Record the value at regular intervals, corresponding to the smallest interval you might want to report on. At the beginning of each minute, or per five minutes, or per hour are common choices. (Use a transaction group with a schedule, or a gateway scheduled event script, storing to a dedicated table. The tag historian has no way to do this.)
When querying, use your database's lead() or lag() function to present adjacent samples in the same row. Wrap that query as a subquery so the outer SQL can compute (nextValue + rolloverConstant - priorValue) % rolloverConstant. This modulus gives you the delta from one row to the next, properly accounting for rollover.
Use your database's sum() function with suitable GROUP BY clause to get the true total for any desired grouped time period.
For me, the simplest to implement, maintain, and explain has been to add a couple of new variables. I've used this pattern often in lots of different technologies. It is pretty easy to implement in Ignition.
Rename your flow meter current value something like FM1027_Raw
Create a new numeric value to count the number of rollovers, e.g., FM1027_RolloverCount
Create a new numeric value to hold the value at rollover (e.g., 99999900 or probably 99999900 + 100?), e.g., FM1027_RolloverValue
Add some code to detect a rollover and increment the rollover count
Create a new derived value with the calculated total, e.g., FM1027_TotalFlow = FM1027_RolloverCount * FM1027_RolloverValue + FM1027_Raw
Keep a history of the total flow. Do all of your calculations with total flow. Show total flow on the screens and reports.
(Keep the history of rollover count and rollover value so that you can troubleshoot and investigate.)
Meh. Not simpler, IMNSHO. Simply logging the odometer at regular intervals captures everything needed to get the right answer, every time. And is tolerant of skipped log events, immune to Ignition restart transients, and is the absolute best format for any random non-Ignition IT person with SQL experience to understand and assist with.
Agree to disagree. Generally my audience isn't an IT person with recent SQL experience who knows LEAD and LAG in an engine that supports it. And rollovers have ... sometimes been a complete mess and I get tired of reconstructing the complete history. As I said, that's my experience and my scars.
