Need help to create downtime data

[ismaan1998]

Hey Team, we've input data like -

Where plc_id is the id of a particular PLC zero-speed - 0 means machine is running, 1 means machine is stopped.

We want to calculate the downtime and store it into the sql. Here is the stream analytics query We've written -

WITH SelectPreviousEvent AS
(
SELECT
    *,
    LAG([time]) OVER (LIMIT DURATION(hour, 24)) as previous_time,
    LAG([zero-speed]) OVER (LIMIT DURATION(hour, 24)) as previous_speed,
    LAG([plc_id]) OVER (LIMIT DURATION(hour,24)) as previous_plc,
    [plc_id]
FROM [iot-input] TIMESTAMP BY [time]
)
-- selects time when current_speed is equal to 1 and previous_speed is equal to 0
SELECT 
    LAG(time) OVER (LIMIT DURATION(hour, 24) WHEN previous_speed = 0 ) [started_time],
    previous_time [end_time],
    [plc_id],
    [created_by] = 'Stream Analytics',
    [updated_by] = 'Stream Analytics',
    [duration] = DATEDIFF(second, LAG(time) OVER (LIMIT DURATION(hour, 24) WHEN previous_speed = 0 ), previous_time)
INTO [sql-output]
FROM SelectPreviousEvent
WHERE
    [zero-speed] = 0
    AND previous_speed = 1

It is working as expected when there is data from only one plc_id.

Output -

And we've verified this downtime with customer, they said its correct.

But if we want to calculate downtime for multiple PLCs, like if there is one more PLC called plc5 and it is also sending the data like plc4, we're getting unexpected results.

I've written few other queries but none of them worked. I couldn't get much help from documentation on this scenario. Let me know what details you need on this more.

Thanks in advance.

[Fleid]

Hey @ismaan1998,

I will look at this question on StackOverflow for a wider reach. I'm closing this issue as it doesn't relate to the content of this repo. See you on StackOverflow ;)

please-close

[ismaan1998]

I see there was help-wanted label, that's why I asked here. Thanks for replying on stackoverflow, I'll try it and let you know on stack overflow. Thanks.