-1

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

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

The required entry in the logging table is

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

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

Charlieface
  • 52,284
  • 6
  • 19
  • 43
IoTian
  • 37
  • 5
  • So is your problem then that the new set of data is missing the existing data? Perhaps you should just `UNION ALL` the last row from the previous set of data if necessary. You also need a unique way of identifying a particular entry/exit pair, in order to modify the `NULL` – Charlieface Aug 23 '22 at 11:54
  • I didn't see the problem at all, it works fine to me. Could you please specify the expected result? – Helio Aug 29 '22 at 16:09
  • The issue is when a new set of data is processed, it doesn't take into account the result of the earlier processed sets of data . I worked out solution which I have given in the answer – IoTian Aug 31 '22 at 08:06

1 Answers1

0

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.

IoTian
  • 37
  • 5