Welcome to the new Schneider Electric Community

It's your place to connect with experts and peers, get continuous support, and share knowledge.

  • Explore the new navigation for even easier access to your community.
  • Bookmark and use our new, easy-to-remember address (community.se.com).
  • Get ready for more content and an improved experience.

Contact SchneiderCommunity.Support@se.com if you have any questions.

Close
Invite a Co-worker
Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
Send Invite Cancel
84631members
353954posts

SQL: Max including Last

EcoStruxure Geo SCADA Expert Forum

Find out how SCADA systems and networks, like EcoStruxure Geo SCADA Expert, help industrial organizations maintaining efficiency, processing data for smarter decision making with IoT, RTU and PLC devices.

MikeForshock
Lieutenant JG
Lieutenant JG
0 Likes
4
2054

SQL: Max including Last

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

 

4 Replies 4
JChamberlain
Schneider Alumni (Retired)
Schneider Alumni (Retired)
0 Likes
3
2050

Re: SQL: Max including Last

 

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

 

image.png

 

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
Lieutenant JG
Lieutenant JG
0 Likes
2
2038

Re: SQL: Max including Last

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
Spock
Spock
1
2024

Re: SQL: Max including Last

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 😉


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
MikeForshock
Lieutenant JG
Lieutenant JG
0 Likes
0
2017

Re: SQL: Max including Last

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 😉