CODEWITHSUNDEEP
sqlsql-server-2008
wrofe
wrofe

Reputation: 13

SQL - Finding sequence of events

I need some help identifying a sequence of events in SQL Server 08 R2. This is the sample data:

    ID | SampleTime | SampleValue | CycleNum
    1  | 07:00:00   | 10          |
    2  | 07:02:00   | 10          |
    3  | 07:05:00   | 10          |
    4  | 07:12:00   | 20          |
    5  | 07:15:00   | 10          |
    6  | 07:22:00   | 10          |
    7  | 07:23:00   | 20          |
    8  | 07:30:00   | 20          |
    9  | 07:31:00   | 10          |

I have used the following as a guide, link , but it doesn't give the required output

The rules are:

Example Output

    ID | SampleTime | SampleValue | CycleNum
    1  | 07:00:00   | 10          |  1
    2  | 07:02:00   | 10          |  1
    3  | 07:05:00   | 10          |  1
    4  | 07:12:00   | 20          |  1
    5  | 07:15:00   | 10          |  2
    6  | 07:22:00   | 10          |  2
    7  | 07:23:00   | 20          |  2
    8  | 07:30:00   | 20          |  2
    9  | 07:31:00   | 10          |  3

Test Table

CREATE TABLE myTable (ID INT IDENTITY, SampleTime DATETIME, SampleValue INT, CycleNum INT)
INSERT INTO myTable (SampleTime, SampleValue)
VALUES ('07:00:00',10),
       ('07:02:00',10),
       ('07:05:00',10),
       ('07:12:00',20),
       ('07:15:00',10),
       ('07:22:00',10),
       ('07:23:00',20),
       ('07:30:00',20),
       ('07:31:00',10)

Upvotes: 1

Views: 1241

Answers (1)

Pons
Pons

Reputation: 1111

Try this... this will give the mapping of ID and CYCLENUM

WITH EVE_DATA AS (
  SELECT ID
    , SAMPLETIME
    , SAMPLEVALUE
    , CASE 
        WHEN (SAMPLEVALUE - lag(SAMPLEVALUE, 1, 0) over (order by SAMPLETIME ASC)) = -10
        THEN 1
        ELSE 0
    END AS START_IND
  FROM
  MY_TABLE
)
SELECT T1.id
  , SUM(T2.START_IND) + 1 AS CycleNum 
FROM EVE_DATA T1
  JOIN EVE_DATA T2
    ON T1.ID >= T2.ID
GROUP BY T1.ID
ORDER BY T1.ID;

Upvotes: 3

Related Questions