A question again on cursors in SQL Server-CodePudding

I am reading data using modbus The data contains status of the 250 registers in a PLC as either off or on with the time of reading as the time stamp. The raw data received is stored in table as below where the column register represents the register read and the column value represents the status of the register as 0 or 1 with time stamp. In the sample I am showing data for just one register (ie 250). Slave ID represents the PLC from which data was obtained

I need to populate one more table Table_signal_on_log from the raw data table. This table should contain the time at which the value changed to 1 as the start time and the time at which it changes back to 0 as end time. This table is also given below

I am able to do it with a cursor but it is slow and if the number of signals increases could slow down the processing. How could I do without cursor. I tried to do it with set based operations I couldn't get one working. I need to avoid repeat values ie after recording 13:30:30 as the time at which signal becomes 1, I have to ignore all entries till it becomes 0 and record that as end time. Again ignore all values till becomes 1. This process is done once in 20 seconds (can be done at any interval but presently 20). So I may have 500 rows to be looped through every time. This may increase as the number of PLCs connected increases and cursor operation is bound to be an issue

Raw data table

 SlaveID    Register    Value   Timestamp   ProcessTime
    -------------------------------------------------------
    3       250         0       13:30:10    NULL
    3       250         0       13:30:20    NULL
    3       250         1       13:30:30    NULL
    3       250         1       13:30:40    NULL
    3       250         1       13:30:50    NULL
    3       250         1       13:31:00    NULL
    3       250         0       13:31:10    NULL
    3       250         0       13:31:20    NULL
    3       250         0       13:32:30    NULL
    3       250         0       13:32:40    NULL
    3       250         1       13:32:50    NULL

Table_signal_on_log

SlaveID   Register   StartTime    Endtime   
  3        250        13:30:30    13:31:10
  3        250        13:32:50     NULL     //value is still 1

CodePudding user response：

This is a classic gaps-and-islands problem, there are a number of solutions. Here is one:

Get the previous Value for each row using LAG
Filter so we only have rows where the previous Value is different or non-existent, in other words the beginning of an "island" of rows.
Of those rows, get the next Timestamp for eacc row using LEAD.
Filter so we only have Value = 1.

WITH cte1 AS (
    SELECT *,
      PrevValue =  LAG(t.Value) OVER (PARTITION BY t.SlaveID, t.Register ORDER BY t.Timestamp)
    FROM YourTable t
),
cte2 AS (
    SELECT *,
      NextTime = LEAD(t.Timestamp) OVER (PARTITION BY t.SlaveID, t.Register ORDER BY t.Timestamp)
    FROM cte1 t
    WHERE (t.Value <> t.PrevValue OR t.PrevValue IS NULL)
)
SELECT
  t.SlaveID,
  t.Register,
  StartTime = t.Timestamp,
  Endtime = t.NextTime
FROM cte2 t
WHERE t.Value = 1;

db<>fiddle