AnsweredAssumed Answered

PI OLEDB - VB - Start Time and End Time

Question asked by YoannCollinSubiron on May 24, 2017
Latest reply on Jun 1, 2017 by yyang

Hello everyone,

I am able to extract the data from PI using a query in Excel.

I have for my tag a list of timestamp (which is the start time) and a value that represent an alarm number.

I use a PIdatalink formula to find the end Time, it's when the alarm number change.

This formula is really time consuming.

 

Does anyone knows if it's possible to do that in a query and if it will be much more faster?

Thanks!

Yoann

 

 

 

----------------------------------

Query:

SELECT DISTINCT left(NU.tag,9) AS "Turbine Number", Nu.time as "Timestamp", NU.value AS "Status State"  FROM (SELECT tag, time, value, _index  FROM piarchive..picomp2 WHERE tag = 'LDTE-WT02-KPIS_FINALFIRSTFAULTS' AND time >= '01/12/2016' ) NU  OPTION (FORCE ORDER)

 

 

PIdatalink formula:

PINComFilDat('LDTE-WT02-KPIS_FINALFIRSTFAULTS';[ThisLine][Timestamp];1;"";'LDTE-WT02-KPIS_FINALFIRSTFAULTS'<>[ThisLine][StatusState];0;1;"ServerPI";"inside")

 

Red column = query

Green column = PIdatalink formula

  

 

 

Turbine NumberTimestampStatus StateEndTime
LDTE-WT0207/12/2016
  08:55
400107/12/2016
  08:59
LDTE-WT0207/12/2016
  08:59
007/12/2016
  13:42
LDTE-WT0207/12/2016
  13:42
99707/12/2016
  14:42
LDTE-WT0207/12/2016
  14:42
007/12/2016
  19:03
LDTE-WT0207/12/2016
  19:03
99707/12/2016
  19:44

Outcomes