AnsweredAssumed Answered

Convert PIDatalink Event frame Query to SQL Query

Question asked by Mark.Andrews on Mar 28, 2017
Latest reply on Mar 28, 2017 by kduffy

I have a batch process that I wish to calculate OEE. I have configured it in Excel using the Pi data link event Function and it works well. Event Frame Query Below

=PIEFDat("Database",Sheet1!$B$1,Sheet1!$B$2,0,"*","3FL Cooking","*","*","*","","","ending in range","start time ascending","","","","","","","","","","","","","","","","{EN},{ST},{ET},{DU},Time between Cooks,Unique Cook ID","","",0,0)


Sheet1!$B$1 = Search StartTime

Sheet1!$B$2 = Search EndTime

3FL Cooking = Event template

Display Columns

EN = EventName, ST=StartTime,ET=EndTime,DU=Duration, Time Between Cooks, Unique Cook ID




I have compiled the following SQL query:

SELECT ef.Name, ef.StartTime, ef.EndTime, (ef.EndTime - ef.StartTime) [Duration], s.Value [Cook Gap]


FROM [Database].[EventFrame].[EventFrameTemplate] eft


INNER JOIN [Database].[EventFrame].EventFrame ef ON ef.EventFrameTemplateID = eft.ID


INNER JOIN [Database].[EventFrame].EventFrameAttribute efa ON efa.EventFrameID = ef.ID


INNER JOIN [Database].[Data].[EventFrameSnapshot] s ON s.EventFrameAttributeID = efa.ID


WHERE eft.Name = N'3fl Cooking'


AND ef.EndTime BETWEEN 't-90d' AND 't'


AND efa.Name = 'Time Between Cooks'





How do I add further columns for 'Unique Cook ID' and possibly some others?