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 WHERE tag LIKE 'EMS.STATUS.RFE1.*.STAT.STAT' 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.