Rank: Administration
Groups: Administration
Joined: 6/20/2008
Posts: 409
Location: Cheshire, United Kingdom.
|
Next example will demonstrate how to get the average value from the PI archives by simply switching to the PIavg table provided by PI ODBC.
The structure of our SQL query changes slightly from previous examples, for the average to work correctly we need to provide a start and end time. To achieve this via ODBC we check for the "time" between two dates.
Our SQL now looks like this:
SELECT pctgood, value FROM PIavg WHERE TAG='SINUSOID' AND time >=DATE('17-Nov-08 09:00') AND time <=DATE('18-Nov-08 09:00')
I have highlighted a couple of things for your attention.
- We are retrieving the pctgood value for the average result (as per PI-Datalink average call).
- The "PIavg" table is used.
- Our start and end time is used.
Our query will return the 1 day average value of sinusoid. This is equivalent to PI-SDK "PIPoint.Data.Summary" using "PISDK.ArchiveSummaryTypeConstants.astAverage" and PI-Datalink "PI -> Calculated Data" and setting "Calc. Mode" to Average.
Code:Sub RetrieveValues()
If PIODBC_Connection Then
Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
Set RS = Conn.Execute("SELECT pctgood, value FROM PIavg WHERE TAG='SINUSOID' AND time >=DATE('17-Nov-08 09:00') AND time <=DATE('18-Nov-08 09:00')")
RS.MoveFirst
While Not RS.EOF
Debug.Print RS.Fields("value").Value & " (" & RS.Fields("pctgood").Value & "% Good)"
RS.MoveNext
Wend
Set RS = Nothing
Else
End If
End Sub
 OSIsoft PI System SpecialistsPI consultancy on PI Systems, PISDK, AFSDK, OLEDB etc and PI custom developments. Well pretty much anything to do with PI!
|
Rank: Administration
Groups: Administration
Joined: 6/20/2008
Posts: 409
Location: Cheshire, United Kingdom.
|
The next step would be to split up the daily average into hourly averages for the same time period. To achieve this the SQL query has 2 simple changes, the "time" column is retrieved and we simply add on an additional clause "timestep".
Our query now looks like (changes from Get Average values in Bold):
SELECT pctgood, value, time FROM PIavg WHERE TAG='SINUSOID' AND time >=DATE('17-Nov-08 09:00') AND time <=DATE('18-Nov-08 09:00') AND timestep = RELDATE('1h')
The function RELDATE converts a PI relative time string to an internal relative time
To implement this:
Code:Sub RetrieveValues()
If PIODBC_Connection Then
Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
Set RS = Conn.Execute("SELECT pctgood, value, time FROM PIavg WHERE TAG='SINUSOID' AND time >=DATE('17-Nov-08 09:00') AND time <=DATE('18-Nov-08 09:00') AND timestep = RELDATE('1h')")
RS.MoveFirst
While Not RS.EOF
Debug.Print RS.Fields("value").Value & " @ " & RS.Fields("time").Value & " (" & RS.Fields("pctgood").Value & "% Good)"
RS.MoveNext
Wend
Set RS = Nothing
Else
End If
End Sub
Simple and powerful.OSIsoft PI System SpecialistsPI consultancy on PI Systems, PISDK, AFSDK, OLEDB etc and PI custom developments. Well pretty much anything to do with PI!
|
