This is continuation of my previous question A question again on cursors in SQL Server.

To reiterate, I get values from a sensor as 0 (off) or 1(on) every 10 seconds. I need to log in another table the on times ie when the sensor value is 1.

I will process the data every one minute (which means I will have 6 rows of data). I needed a way to do this without using cursors and was answered by @Charlieface.

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

The raw data set and desired outcome are as below. Here register 250 represents the sensor and value presents the value as 0 or 1 and time stamp represents the time of reading the value

The required entry in the logging table is

The solution given works fine but when the next set of data is processed, the exiting open entry (end time is null) is to be considered.

If the next set of values is only 1 (ie all values are 1), then no entry is to be made in the log table since the value was 1 in the previous set of data and continues to be 1. When the value changes 0 in one of the sets, then the end time should be updated with that time. A fresh row to be inserted in log table when it becomes 1 again

CodePudding user response：

I solved the issue by using a 'hybrid'. I get 250 rows (values of 250 sensors polled) every 10 seconds. I process the data once in 180 seconds. I get about 4500 records which I process using the CTE. Now I get result set of around 250 records (a few more than 250 if some signals have changed the state). This I insert into a #table (of the table being processed) and use a cursor on this #table to check and insert into the log table. Since the number of rows is around 250 only cursor runs without issue.

Thanks to @charlieface for the original answer.