What is an equivalent sql query for the below Excel add-in functions PICalcVal(Tagname,begin time, end time, "average", 1, 0,"server1") PIAdvCalcDat(Tagname,"y","t","15m","average (time-weighted)&quo

Hello,

If you intend to use query PI Point data, you need to use PI OLEDB Provider. An example query would be like below.

SELECT *
FROM [piarchive]..[piavg]
WHERE tag = 'tagname'  
	AND time BETWEEN '*-1h' AND '*'
	AND timestep = '15m'
	and pctgood >=100

PICalcVal function is deprecated and PIAdvCalcVal is its successor, so the SQL query is the same. Querying piavg table by default returns time-weighted average. If event-based one is needed, you can query pimean table or add calcbasis = "EventWeighted" to you piavg query.

However, I would recommend using PI SQL Client if you have your data in PI Asset Framework. There, you can use built-inTable-Valued Functions (Get Summary/GetSummaries).

Hello,

Thank you for the information you have provided. Per your suggestion I tried to execute the below query

SELECT *

FROM PI.PIArchive..piavg t1

WHERE t1.tag = 'tagname'  

AND t1.time BETWEEN '12-Jun-2023 00:00:00' AND '13-Jun-2023 00:00:00'

AND t1.timestep = '15m'

and t1.pctgood >=100 ;

I am getting the below error.

"Conversion failed when converting date and/or time from character string."

Please let me know how to fix this error.

Hi,

I executed the query with the same timestamps and it worked just fine. Where are you running the query? I did it in PI SQL Commander Lite but as far as I know this error can occur when running queries in different clients e.g. via Linked Server.

Hello,

I am executing my query in sql client database. I did find a solution posted by one of the users that uses OpenQuery construct. That seems to work for me. I would like to find out what are the database columns that are associated with the parameters in the below Excel add-in function so I can add additional filter conditions to the WHERE clause.

PIAdvCalcDat(Tagname,"y","t","15m","average (time-weighted)","time-weighted",0,1,65,"server1")

Hello,

You can see the meaning of those parameters in the PI DataLink manual:

PIAdvCalcDat()