SQL conditional Where with limits and previous val

Question

I have a table which contains sensor data data1 and the allowed (alarm) limits for that sensor data data1high and data1low. I wish to create a view where only those data1 values are listed which are the first to move outside the limits (i.e. an alarm condition) or moved back in within "safe" limits (alarm condition no longer present).

Here is a typical table:

| id | data1 | data1high | data1low | 
|----|-------|-----------|----------|
|  1 |    60 |       200 |      100 |
|  2 |    80 |       200 |      100 |
|  3 |   123 |       200 |      100 |
|  4 |   150 |       200 |      100 |
|  5 |    60 |       200 |      100 |
|  6 |    60 |       200 |      100 | 
|  7 |   150 |       200 |      100 |
|  8 |    40 |       200 |      100 |
|  9 |    58 |       200 |      100 |
| 10 |    62 |       200 |      100 |
| 11 |   300 |       200 |      100 | 

The logic is that values within where data1 < data1low OR data1 > data1high are in the alarm condition and should be listed.

For example,

| id | data1 |
|----|-------|
|  1 |    60 |
|  2 |    80 |
|  5 |    60 |
|  6 |    60 |
|  8 |    40 |
|  9 |    58 |
| 10 |    62 |
| 11 |   300 |

The table above shows all the values in the alarm state. I do not want this, only those that have just transitioned into that state and those which first values where the data1 goes back to within safe limits, so my ideal view would be:

| id | data1 | data1high | data1low | 
|----|-------|-----------|----------|
|  1 |    60 |       200 |      100 |
|  3 |   123 |       200 |      100 |
|  5 |    60 |       200 |      100 | 
|  7 |   150 |       200 |      100 |
|  8 |    40 |       200 |      100 |
| 11 |   300 |       200 |      100 |

id 1 is in alarm state so listed, id 2 is omitted because that was still in the alarm state, id 3 is listed because that is the next value to be back in limits, id 4 is omitted because that is still within limits, id 5 is listed because that is back outside limits etc ...

Answer 1

Here is another solution:

DECLARE @t TABLE
    (
      id INT ,
      data1 INT ,
      data1high INT ,
      data1low INT
    )

INSERT  INTO @t
VALUES  ( 1, 60, 200, 100 ),
        ( 2, 80, 200, 100 ),
        ( 3, 123, 200, 100 ),
        ( 4, 150, 200, 100 ),
        ( 5, 60, 200, 100 ),
        ( 6, 60, 200, 100 ),
        ( 7, 150, 200, 100 ),
        ( 8, 40, 200, 100 ),
        ( 9, 58, 200, 100 ),
        ( 10, 62, 200, 100 ),
        ( 11, 300, 200, 100 )


;WITH t AS(SELECT *, CASE WHEN data1 < data1low or data1 > data1high THEN 1 ELSE 0 END b
           FROM @t)
SELECT * FROM t t1 
WHERE b <> (SELECT TOP 1 b FROM t t2 WHERE t2.id < t1.id ORDER BY t2.id DESC) OR
      id = (SELECT MIN(id) FROM t) OR
      id = (SELECT MAX(id) FROM t)    

Answer 2

You can use a Recursive CTE to iterate over the rows and compare one row to the previous row, applying the logic of what you classify as a transition.

Looking at your desired output though, I don't think id = 10 should appear in the list as it hasn't transitioned.

Here's a sample that you can run in isolation to test:

CREATE TABLE #Data1
    (
      [id] INT ,
      [data1] INT ,
      [data1high] INT ,
      [data1low] INT
    );

INSERT  INTO #Data1
        ( [id], [data1], [data1high], [data1low] )
VALUES  ( 1, 60, 200, 100 ),
        ( 2, 80, 200, 100 ),
        ( 3, 123, 200, 100 ),
        ( 4, 150, 200, 100 ),
        ( 5, 60, 200, 100 ),
        ( 6, 60, 200, 100 ),
        ( 7, 150, 200, 100 ),
        ( 8, 40, 200, 100 ),
        ( 9, 58, 200, 100 ),
        ( 10, 62, 200, 100 ),
        ( 11, 300, 200, 100 );
WITH    cte
          AS ( SELECT TOP 1
                        id ,
                        data1 ,
                        data1high ,
                        data1low ,
                        CASE WHEN data1 < data1low
                                  OR data1 > data1high THEN 1
                             ELSE 0
                        END AS Transitioned
               FROM     #Data1
               ORDER BY id
               UNION ALL
               SELECT   #Data1.id ,
                        #Data1.data1 ,
                        #Data1.data1high ,
                        #Data1.data1low ,
                        CASE WHEN cte.data1 < cte.data1low
                                  AND #Data1.data1 < #Data1.data1low THEN 0
                             WHEN cte.data1 > cte.data1high
                                  AND #Data1.data1 < #Data1.data1high THEN 0
                             WHEN cte.data1 BETWEEN cte.data1low AND cte.data1high
                                  AND #Data1.data1 BETWEEN #Data1.data1low
                                                   AND     #Data1.data1high
                             THEN 0
                             WHEN cte.Transitioned = 1
                                  AND #Data1.data1 BETWEEN #Data1.data1low
                                                   AND     #Data1.data1high
                             THEN 1
                             ELSE 1
                        END AS Transitioned
               FROM     #Data1
                        INNER JOIN cte ON cte.id + 1 = #Data1.id
             )
    SELECT  *
    FROM    cte
    WHERE   cte.Transitioned = 1

DROP TABLE #Data1

Within the CTE, a column is added to mark rows that have transitioned. The CASE WHEN clauses contain what I can gauge as the logic you require to asses if a transition has taken place compared to the previous row.

At the end of the CTE, you can simply select all rows where Transitioned = 1, to produce:

id  data1   data1high   data1low    Transitioned
1   60      200         100         1
3   123     200         100         1
5   60      200         100         1
7   150     200         100         1
8   40      200         100         1
11  300     200         100         1

Working Demo SQL Fiddle

Answer 3

If you're using SQL-Server 2012, you can use the LAG function:

If I understood your problem correctly, you want to get all records that changed from being in alarm to not being in alarm. If that is the case, shouldn't be 11 not in the result set? The last change is 8. Records in between ( 9 and 10) is still in alarm, so is 11, so this should not be included.

WITH CteAlarm AS(
    SELECT *,
        alarm = CASE WHEN data1 < data1low OR data1 > data1high THEN 1 ELSE 0 END
    FROM test
),
Cte AS(
    SELECT *,
        prevAlarm = LAG(alarm) OVER(ORDER BY id)
    FROM CteAlarm
)
SELECT * 
FROM Cte
WHERE 
    alarm <> prevAlarm
    OR (prevAlarm IS NULL AND alarm = 1)

SQL Fiddle