Asle Frantzen

Most time effective query for retrieving daily max tag value for a period of time?

Discussion created by Asle Frantzen Champion on Jan 22, 2010
Latest reply on Jan 26, 2010 by Asle Frantzen

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?

Outcomes