ChewCheeLim

A way to get Min\Max\Avg values without going to 'PI servers' in SQL commander

Discussion created by ChewCheeLim on May 2, 2012
Latest reply on May 8, 2012 by Ahmad Fattahi

Hi, I would like to retrieve hourly min, max & avearge values for an element attribute. Can I do this without going to PI Servers?

 

Example:

 

Currently this is what I have to retrieve average value for PI TAG CDT158

 

 

 

SELECT
    tag, format(time,'yyyy-MM-dd HH:mm:ss'), value 
FROM
    piarchive..piavg
WHERE
    time BETWEEN '2012-04-04 00:00:00.000' AND '2012-04-04 23:00:00.000'
AND
    tag = 'CDT158' 
AND
    timestep = '1h'

 

I wanted to do the same in 'AF Servers'. And this is my attempt to pull the velue out for PI TAG CDT158 which mapped to m022_1 in AF. The AF schema doesn't expose average\Min\Max tables like in 'PI Servers' in SQL commander. What are my options?

 

 

 

Many thanks!

 
SELECT
    ea.Path + ea.Name ElementAttribute, FORMAT(i.time, 'yyyy-MM-dd HH:mm:ss'), i.Value
FROM
    Asset.ElementAttribute ea 
INNER JOIN 
    Data.ft_InterpolateRange i ON i.ElementAttributeID = ea.ID
WHERE
    ea.ElementId = 'a1572375-2bfc-4dd6-af60-971addbd06f6'
AND
    ea.Name = 'm022_1'
AND
    i.StartTime = DATE('2012-04-04 00:00:00.000') 
AND
    i.EndTime = DATE('2012-04-04 23:00:00.000')
AND
    i.TimeStep = TIME('1h')
OPTION (FORCE ORDER)

 

Outcomes