How do I query daily averages of an AF Attribute for the past 30 days?

Discussion created by j.rao on Jan 29, 2014
Latest reply on Jan 30, 2014 by j.rao

I want to query all the attributes of an element in AF much like the following query (which works for a PI Tag). 

SELECT tag, time, value
FROM piarchive..piavg
WHERE tag = 'sinusoid'
AND time BETWEEN '*-30D' AND '*'
AND timestep = '1d'
AND calcbasis = 'EventWeighted'

In other words, I would like the attributes returning daily averages for the past 30 days for instance. How do I go about that? I saw some other posts on this forum that were using C# or VB.Net. Can we have a pure SQL solution using OLEDB Enterprise?


Desired output :

Timestamp   | Element Name      | Attribute         | Value (24 hour average)

2014-01-01     Element 1              Diff Press               634

2014-01-02     Element 1              Diff Press               568

2014-01-03     Element 1              Diff Press               602

 How do I extend this to get the desired output much like using piarchive..piavg? (I am using AF Linked Server for to query).

SELECT * from Openquery("TESTServer-AF", 'SELECT distinct e.Name as "Equipment Name", ea.Name, e.Id as EquipmentId, REPLACE(ea.Path,''\'','''') as "Attribute",
s.Value as "Tag Name", i.*
FROM [NuGreen].[Asset].[ElementHierarchy] eh 
INNER JOIN [NuGreen].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID 
INNER JOIN [NuGreen].[Data].[Snapshot] s ON s.ElementAttributeID = ea.ID 
inner join [NuGreen].Asset.Element e on eh.ElementId = e.ID 
inner join [NuGreen].Asset.ElementTemplate et on e.ElementTemplateId = et.Id,
[NuGreen].[Data].[ft_InterpolateDiscrete] i
where (et.name = ''Injector template'' and eh.path = ''\NuGreen\Injector\''
   -- function tables require the WHERE clause to contain restrictions for all function arguments:
AND i.ElementAttributeID = ea.ID -- first InterpolateDiscrete TVF argument
AND i.Time IN (N''*-1d'') -- second InterpolateDiscrete TVF argument

 Much appreciated!


- Jagdish