Hi there,

I have had a request from a customer to extract some data for the past year. What they would like is to have a data dump where they could see the Maximum value of an analog tag for each hour for the last year.

To clarify, 1 value per hour - which is the maximum value for that hour and the report would have 24 x 365 rows.

Can someone help me write this query? I am assuming that I need to do an aggregate query (max) grouping by the hour for each day, but honestly not sure how to write it.

Thanks in advance,

Jamie

OSIsoft vCampus is a subscription-based, online offering that consists of providing everything people need to develop applications on the PI System.
We invite you to take a "tour" of the OSIsoft Virtual Campus - also feel free to consult the FAQ  or contact OSIsoft vCampus for more details.

I agree with Burnikell1 that for a one off task that would be my approach. Except that I would not use * as this could provide misleading (and possibly different) data depending on what time you recalculated the sheet.

A method:

Place the tag in A1

In the first column (A2 downwards) populate the days.

In the row for your first day select all 24 cells to hold the data (B2:Y2) and build this Calculated Data formula:

=PICalcDat($A$1,$A2,$A2+1,"1 hour","maximum",1,2,"prepidata")

This will make 365 calls to PI for the data which will be slower than Burnikell1's suggestion of grabbing all 8k values in one go. Our PI 2 system returned the data for one tag in 50 seconds.

Once you have that cracked you can do max/min as well and impress your customer.