AnsweredAssumed Answered

PI OLEDB, using TimeEq function when querying AF data

Question asked by gseim on Jan 6, 2015
Latest reply on Jan 7, 2015 by gseim

I have this query which works perfectly towards a PI server


SELECT tag, TimeEq(tag,'*-1d','*','True')FROM[pipoint].[pipoint2]where tag like'%szms%'

 

My question is: Is this available when doing queries towards an AF server (PIOLEDB/E)? I would like tag to be replaced by an attribute, since I want to report based on the asset model.

 

I would like to report how long an assets attribute has been a certain state. I will use this query in a report written using SSRS.

 

This query gives me the archived data.

Select ea.id, e.Name, ea.Name, a.time, a.ValueInt, a.value
 from [DIMS QA].[Data].[Archive] A 
 INNER JOIN [DIMS QA].[Asset].[ElementAttribute] EA ON EA.Id = A.ElementAttributeId
 INNER JOIN [DIMS QA].[Asset].[Element] E ON E.ID = EA.ElementID
 WHERE a.Time BETWEEN N'*-2d' AND N'*'
 AND A.ElementAttributeId in(
 SELECT EA.ID
 FROM [DIMS QA].[Asset].[ElementTemplate] ET
 INNER JOIN [DIMS QA].[Asset].[Element] E ON E.ElementTemplateId = ET.ID
 INNER JOIN [DIMS QA].[Asset].[ElementAttribute] EA ON EA.ElementId = E.ID
 INNER JOIN [DIMS QA].[Asset].[ElementHierarchy] EH ON EH.ElementId = E.ID
 WHERE ET.Name = 'SmartZone'
 AND EA.Name = 'Enabled'
 )
 order by a.time
OPTION(ALLOWEXPENSIVE)


Result:

IDNameNameTimeValueIntValue
00005099-0000-0000-f876-000000000000DDMEnabled05.01.2015 15:00:171True
00005099-0000-0000-f876-000000000000DDMEnabled05.01.2015 15:15:200False
00005099-0000-0000-f876-000000000000DDMEnabled05.01.2015 15:22:331True
00005099-0000-0000-f876-000000000000DDMEnabled06.01.2015 08:18:280False
00005099-0000-0000-f876-000000000000DDMEnabled06.01.2015 08:19:041True
00005099-0000-0000-f876-000000000000DDMEnabled06.01.2015 08:29:070False
00005099-0000-0000-f876-000000000000DDMEnabled06.01.2015 08:31:201True
00005099-0000-0000-f876-000000000000DDMEnabled06.01.2015 08:34:070False
00005099-0000-0000-f876-000000000000DDMEnabled06.01.2015 08:50:181True
00005099-0000-0000-f876-000000000000DDMEnabled06.01.2015 08:52:590False
00005099-0000-0000-f876-000000000000DDMEnabled06.01.2015 09:00:121True
00005099-0000-0000-f876-000000000000DDMEnabled06.01.2015 09:03:080False


From this query I would like one row:


DDM - Time true


If I run the query for the tag directly towards the PI server I get, which I would love to get from PIOLEDB/E, but for the DDM asset...

 

SZMS-DDM-DISABLED17:29:25


Any suggestions?


Outcomes