I've got a view that lists the times a particular damper is open (in a manufacturing environment). I'm limited to querying this view. Basically, the data looks like this:
dateTime value
9/1/15 0:01 1
9/1/15 0:10 0
9/1/15 1:10 1
9/1/15 2:00 NULL
9/1/15 3:01 0
I need to find every time the damper is open (1) and determine when it closed (0). The nulls we're assuming to be open (same as 1).
What's the best way to find every entry where the value = 1 and then the next 0 after that 1?
It is a Historian DB, but it basically mimics SQL in nearly every way.
Here's the code that wound up working - for posterity. :)
SELECT DateTime, Value,
(SELECT TOP (1) hClose.dateTime FROM history hClose WHERE
hClose.dateTime > history.DateTime
AND hClose.TagName = 'TAGNAMEHERE'
AND hClose.Value < 10
AND wwRetrievalMode = 'average'
AND wwResolution = 600000
ORDER BY dateTime) as closeTime
FROM history
WHERE dateTime BETWEEN '9/1/15 00:00' AND '10/1/15 0:00'
AND tagName = 'TAGNAMEHERE'
AND (VALUE > 10 OR VALUE IS NULL)
AND wwRetrievalMode = 'average'
AND wwResolution = 60000