We are trying to get attribute values of eventframe using SQL commander, seems straightforward but got stuck.
There are 14 attributes of EF and among them, 13 are PI tags and 1 is AF attribute. Out of 13 PI tags, 11 has been configured as below with relative time -1s, one with +1s and left one is @ Start Time in TimeRange:
How should we create a query in SQL commander that will pull out EF with all attribute values?
I have tried as follows and yet to find success:
SELECT ef.Name,ef.StartTime,efa.Name,ea.Value,ea.Time,ef.endTime FROM ( -- Get EventFrame SELECT * FROM [TEST].[EventFrame].[EventFrame] WHERE Name = '<eventframe_name>' ) ef -- Get Attributes of EF by comapring ID INNER JOIN [TEST].[EventFrame].[EventFrameAttribute] efa ON efa.EventFrameID = ef.ID INNER JOIN [TEST].[Data].[EventFrameArchive] ea ON ea.EventFrameAttributeID = efa.ID WHERE ea.Time BETWEEN ef.StartTime-time('10s') AND ef.EndTime OPTION (FORCE ORDER, EMBED ERRORS)
2 attributes of EF - an AF attribute and PI Tag @ StartTime
Ideally it should give all 14 attributes values.
used modified version of Querying event frame related AF attribute historical data with time offset
SELECT ea.Name attribute, d.Value value, d.Time time FROM [TEST].[EventFrame].[EventFrame] ef INNER JOIN [TEST].[Asset].[Element] el ON el.ID = ef.PrimaryReferencedElementID INNER JOIN [TEST].[Asset].[ElementAttribute] ea ON ea.ElementID = el.ID INNER JOIN [TEST].[Data].[Archive] d ON d.ElementAttributeID = ea.ID WHERE ef.Name ='<EventFrame_Name>' AND d.Time BETWEEN ef.StartTime-time('1s') and ef.StartTime+time('1s') ORDER BY ea.Name OPTION (FORCE ORDER, EMBED ERRORS)
Giving partly element attributes which are in EF and which are not both.
Roger Palmen seems you have worked lot on EF in 2016-17. If you can guide where something is wrong will be helpful .