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'

 

OPTION (FORCE ORDER)

 

 

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

Outcomes