Nice and long subject for this one
A client of ours uses PI as a linked server in MS SQL Server, and needs to retrieve the daily maximum value of - let's say - sinusoid, for the last 180 days.
I wanted to provide her with an optimized query, to minimize data being transferred between PI and MSSQL. Since I wanted to utilize PI's build-in methods for this, I found the PE function TagMax - and used that as an expression when querying the piarchive..picalc table. I did manage to get the TagMax, but I got a lot of them. One for every record stored for sinusoid every day.
I tried stripping away the time-part of 'time' - so that I could group by only the date-part. Still, no luck!
So I ended up providing this query, where a lot of data is sent between PI and MSSQL - and then sorted/grouped after arriving to MSSQL:
SELECT DISTINCT (DATEADD(dd, 0, DATEDIFF(dd, 0, time))) AS myDate,value
FROM OPENQUERY(PI, 'SELECT time, value FROM piarchive..picalc WHERE expr = ''TagMax(''''sinusoid'''', ''''t'''', ''''t+24h'''')'' AND time between ''*-180d'' and ''*''')
ORDER BY myDate ASC
So - have I overlooked some essential part of the PI OLEDB manual, or is this actually difficult to achieve?