1

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
Charlieface
  • 52,284
  • 6
  • 19
  • 43
IoTian
  • 37
  • 5
  • 1
    Sample data, and expected results (I don't know what the above is) will help us help you, along with a clear explanation of the logic to get from the sample to the expected. – Thom A Aug 15 '22 at 15:33
  • Data received is as given for signal number 250 as an example. Now I need to my Table_Signal_on_log as Start time 13:30:30 End time 13:31:00 , next row start time 13:32:50 end time null. – IoTian Aug 15 '22 at 15:38
  • That doesn't explain anything. – Thom A Aug 15 '22 at 15:38
  • Similarly the table_signal_off_log will have start time 13:31:10 end time 13:32:40 and so on. Hope I am making myself clear. I need to record when a register / signal turns to 1 and 0 – IoTian Aug 15 '22 at 15:40
  • 2
    No you are not making yourself clear. Please [edit] the *question* (rather than comments) and add sample data/expected results whichever is missing, and please explain the logic clearly. Your existing code would also help. – Charlieface Aug 15 '22 at 15:50
  • Question edited to make it more clear. (hopefully ) – IoTian Aug 16 '22 at 07:02
  • Classic gaps-and-islands problem, try this https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ccb356f1b8bb7161590de440f75d440c. @Larnu Think this can be re-opened, no? – Charlieface Aug 16 '22 at 11:10
  • Thank you very much. It was a very big help. Thanks again – IoTian Aug 16 '22 at 16:32

1 Answers1

1

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

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • I tried to upvote but didn't have the necessary qualification. I have another issue . I insert the result set of cte2 in to another table Table_signal_on_log. When the next set of results are processed, I run into an issue. In the above example, the register 250 has a value of one from 13:32:50 (end time is null). If the next set of data processed has only value 1 for register 250, no row needs to inserted, since the value is one since the last result set. But one more gets inserted. In need to check Table_signal_on_log before inserting the next result set. I tried joins with out success. – IoTian Aug 23 '22 at 07:21
  • I'm not entirely clear, but you should probably post that as a new question. You can stiil accept this (by clicking the check mark) even if you have low reputation. – Charlieface Aug 23 '22 at 11:15
  • Accepted the answer. I will post as a new question – IoTian Aug 23 '22 at 11:18
  • Follow up question is here https://stackoverflow.com/questions/73458106/a-follow-up-question-on-gaps-and-islands-solution – IoTian Aug 23 '22 at 11:44