Need help with SQL for PI OLEDB Query

Discussion created by hjkuyp on Nov 25, 2011
Latest reply on Dec 6, 2011 by spilon

I am looking for some help with the SQL for an OLEDB query.


We are using digital tags to record when a device status is Failed or Normal. I am trying to obtain a list of record a Failed and Normal value over a given time period.


The following query gets me close but is returns every Normal value that occurs after each recorded Failed event for each device tag. What I need is the first Normal value that was recorded for each device that went into a Failed state.

SELECT top 1 f.tag, f.time, n.time
FROM piarchive..picomp2 n,
 (SELECT tag, time 
 FROM piarchive..picomp2 
  AND DIGSTRING(CAST(value as Int32)) = 'Failed' 
  AND time >= 't-30d'
 ORDER BY time) f
WHERE n.tag = f.tag AND n.time > f.time
ORDER BY f.time, n.time, f.tag



Any help would be greatly appreciated.