Named Query for Machine Runtime calculation (Example)

Ignition
,
1

Hi all,

Here is a named query for calculating machine runtime based on a simple 3 state stack light. I have been using SQL bridge module to create a transaction group for every machine.

When machine is running,
RUN_STS = 1, STOP_STS = 0, BREAKDOWN_STS = 0

When machine is stopped,
RUN_STS = 0, STOP_STS = 1, BREAKDOWN_STS = 0

When machine is in breakdown,
RUN_STS = 0, STOP_STS = 0, BREAKDOWN_STS = 1

Following query helped me find time under RUN, STOP and BREAKDOWN condition. Please find an example below.

-- First create a table with delta of original timestamps & lagged timestamps
WITH
    cte1_tbl_with_lagged_ts AS(
        SELECT
           id,
           t_stamp AS current_ts,
           RUN_STS,
           STOP_STS,
           BREAKDOWN_STS,
           -- Now get a time stamp difference between every consecutive entry
           DATEDIFF(second,LAG(t_stamp) OVER(ORDER BY tbl_ffs_en7913_loggr_ndx),t_stamp) AS difference
       
        FROM tbl_machine123_loggr
        WHERE
            t_stamp > :start_ts AND t_stamp < :end_ts
        ),

    cte2_tbl_runtime_calcs AS(
    -- Only select following columns in which RUN_STS = 1
        SELECT
            id,
            RUN_STS,
            difference AS runtime    
        FROM cte1_tbl_with_lagged_ts
        WHERE RUN_STS=1
        ),
    
    cte3_tbl_downtime_calcs AS(
    -- Only select following columns in which STOP_STS = 1
        SELECT
            id,
            STOP_STS,
            difference AS stoptime    
        FROM cte1_tbl_with_lagged_ts
        WHERE STOP_STS=1
        ),
        
    cte4_tbl_breakdowntime_calcs AS(
    -- Only select following columns in which BREAKDOWN_STS = 1
        SELECT
            id,
            BREAKDOWN_STS,
            difference AS breakdowntime    
        FROM cte1_tbl_with_lagged_ts
        WHERE BREAKDOWN_STS=1
        ),
        
    cte5_tbl_poweredofftime_calcs AS(
    -- Only select following columns where no STS signal is present (i.e. all of them are zero)
        SELECT
            id,
            current_ts,
            RUN_STS,
            STOP_STS,
            BREAKDOWN_STS,
            difference AS poweredofftime    
        FROM cte1_tbl_with_lagged_ts
        WHERE RUN_STS=0 AND BREAKDOWN_STS=0 AND STOP_STS = 0
        )

-- Take sum of all timestamp differences
SELECT
    SUM(runtime)/60.0 AS total_runtime
    FROM cte2_tbl_runtime_calcs;
    
/*  SUM(downtime)/60.0 AS total_runtime
    FROM cte3_tbl_downtime_calcs;
*/
/*  SUM(breakdowntime)/60.0 AS total_runtime
    FROM cte4_tbl_breakdowntime_calcs;
*/
2

Your recordings are underconstrained. What happens when all status bits are zero? Or more than one is on?

3

Good question Phil! Yes, here recordings are unconstrained. For a more general solution, it is better to add those constraints. How would you suggest doing that? (also consider the machine is manually switched off at EoD)

This calculation works for my case because of the following reasons.

  • I have adjusted execution scheduling to 1 second & triggered on status change. So I haven't observed many 'all status bit = 0' conditions. There are some instances where we have seen all zeros. But that transition state only stays there for a few milliseconds.
  • The PLC code performs the 'switch off' & 'switch on' actions in a single cycle. So never observed a state where more than one bit is turned on.
4

If you are sure an invalid state will be short-lived, then you can just ignore them. FWIW, I wouldn't use common table expressions this way. Consider the following:

Select
	Status,
	Sum(DateDiff(second, BeginTS, NextTS)) AS Duration
From (
	Select
		id,
		Case When t_stamp < :start_ts Then :start_ts Else t_stamp End As BeginTS,
		Status,
		coalesce(lead(t_stamp) Over (Order By t_stamp), :end_ts) As NextTS
	From (
		Select
			id,
			t_stamp,
			Status
		From (
			Select
				id,
				t_stamp,
				Status,
				coalesce(lead(Status) Over (Order By t_stamp), '') As NextStatus
			From (
				Select
					id,
					t_stamp,
					Case When RUN_STS=1 Then 'Running' When BREAKDOWN_STS=1 Then 'Breakdown' Else 'Stopped' End As Status
				From tbl_machine123_loggr
				Where t_stamp >= (
					Select Top 1 t_stamp
					From tbl_machine123_loggr
					Where t_stamp < :start_ts
					Order By t_stamp Desc
				) And t_stamp < :end_ts
			) expanded_range
		) status_lookahead
		Where Status != NextStatus
	) deduplicated
) ts_lookahead
Group By Status
Order By Status

Can get away without the deduplication subqueries in many cases. I suspect this will run dramatically quicker than the CTEs.

1 Like