SQL: Max including Last

[MikeForshock]

Within ClearSCADA (Geo SCADA Expert...), you can view a historic tag that includes "Max including Last".

Within the SQL interface, I am having trouble finding a similar tag.

Currently have the following SQL, which works great (ish).

(PHP code for reference)

"SELECT MAX(ValueAsInteger) AS \"CountMAX\", MIN(ValueAsInteger) AS \"CountMIN\", MAX(\"RecordTime\") FROM \"CDBHistoric\" WHERE ID = ? AND \"RecordTime\" BETWEEN { ". $f_timestamp[$key]["Start"]." } AND { ". $f_timestamp[$key]["End"]." } GROUP BY \"Id\"";

This will get the values in the database within the specified time frames. But if the most recent MIN is 1AM (Compression), the difference from 12AM to 1AM is 0.

If the last entry was at 11:55 PM, there would be a difference to calculate.

23:55 = 289 (Previous day)

01:00 = 300

Actual Difference = 11

Short of doing a query from the previous time frame for the MAX (289 in the example).

Thanks for your assistance.

Mike

[Anonymous user]

You'd probably want to add a custom historic view with the MaxLast algorithm.

if you're not familiar, Help at System Administration > Server Administration > SQL Query Configuration Settings > Historic Views will explain better than I can here.

[MikeForshock]

That would be okay for future data, but not for already existing data.

Thinking we will need to create a routine to find the last known value before each query, unfortunately doubling the queries.

Thanks.

[BevanWeiss]

I think you're mistaken Mike,

The Historic Views apply to ALL data (which must be historical of course... GeoSCADA Expert doesn't yet predict the future with greater than 50% certainty for half the future events).  So if you ran those queries again referencing the Historic View (which had Max Last in it) then the (historical) data returned would be using Max Last also..

If you have been running some logic with an alternative query (i.e. one which doesn't reference the 'new' Historic View) and storing those results, then that data will be wrong... but it was wrong before Jesse let you know about the world of Historic Views too 😉

[MikeForshock]

I'll see if I can give it a shot and report back.

Thanks.

PS: If I forget to update this post, don't hold it against me 😉