SQL query to calculate Difference

[pratik_jain]

Hello all,
I have a transaction group running on a trigger from PLC. This transaction group records the values and stores them into the DB. The next step i am trying to calculate the difference between two rows of AWN Totalizer and RODI totalizer and store them in a new column as AWN FLOW and RODI FLOW. I have heard about the LAG function but not sure how to go about storing and triggering that query.

Thank You.

[lrose]

You would probably need to write a named query, which you then called from a Gateway Event (perhaps a Tag Change Event).

I don’t think it is possible to do this calculation from a transaction group.

[craigb]

On your DB query to get that table you can add the columns for viewing purposes easily, but they wouldn’t be written to the DB itself. If that’s all you need ?

[pratik_jain]

Yes that would work too. can you please give me more information on how to calculate the difference ?

[craigb]

What are you specifically trying to work out and I’ll see if I can work out the query for you. Will be a good learning experience for me too.

[pratik_jain]

I am trying to calculate the difference between the AWN Totalizer from one day and previous day and store it in a new column in the same table as AWN Flow.

eg, AWN Totalizer on Day2 - AWN totalizer on day 1 store it in as AWN Flow in same row.

[craigb]

I do something similar for production values, difference between today and yesterday, let me see if I can help and apply that to your example.

[jlandwerlen]

You could also do this in scripting, something that may be helpful for you,

Tag history difference over interval

I am trying to find a better way to do production counts over specific intervals using tag history. Example, I have a tag TestCount, right now it’s a memory tag, but typically this would be an OPC tag from a PLC that increments. I can’t use Count aggregationMode because it may not “catch” each transition, example, it may go from 10 to 13. My desire is to be able to chart this data over a period of time, example, 12 hours, with a certain interval, example 1 hour. So, if at the start of the ho…

[craigb]

So I took a different approach to this…

I created a Query Tag Called Last Entry.

Select AWN
From testdb
WHERE id=(SELECT max(id) FROM testdb)

This gets the AWN value from the last entry in the table, assuming id is your incrementing key.

I then created a expression tag…

{[.]FlowMeter}-{[.]Last Entry}

This is assuming your live flow meter value is a tag. So this is taking your real time flow value, subtracting the result from the last DB entry.

The result of this query tag can be put in your transaction group.
You can also use this value then to show a live delta, if anyone wants to see that.

[pratik_jain]

Thank You ! I have created the tags and added them to the transaction group. Will let you in couple of days if this works out.

Appreciate your help !

[craigb]

How did this workout for you ?

[pratik_jain]

It worked out perfectly ! I was able to get the values. Thank you so much !