Hello, so i have been trying to get a runtime of some boolean tags which are set up with the Historian, when turned on or off they are added to the sql table with a timestamp and the intvalue of 1 or 0,
i would like to somehow display the duration these tags spent as an intvalue of 1 but i have been unsuccessful so far.
I feel like there could be an easier way to do this through ignitions features but not that i ould find so I have tried creating a Named Query as follows. (Let me know if a question like this would be better suited to ask on Stack Overflow)
SELECT :datee as 'datee', :datee2 as 'datee2', d.tagid, (Sum( CASE -- subtract the timestamps of 'ON' events WHEN d.intvalue = 1 THEN -1 * d.t_stamp -- add the timestamps of 'OFF' events ELSE d.t_stamp END ) + -- if ended 'ON', so we need to add our endTime parameter CASE WHEN d.EndingState = 1 THEN (:datee2/1000) ELSE 0 END + -- if started 'OFF', so we need to subtract our startTime param CASE WHEN d.StartingState = 0 THEN -1*(:datee/1000) ELSE 0 END) AS TotalRunTime -- Sub query to find starting and ending state for the particular time range FROM (SELECT tagid, intvalue, t_stamp, (SELECT TOP 1 intvalue FROM sqlt_data_1_2022_06 WHERE a.tagid = tagid AND t_stamp >= :datee AND t_stamp <= :datee2 ORDER BY t_stamp ASC ) AS StartingState, (SELECT TOP 1 intvalue FROM sqlt_data_1_2022_06 WHERE a.tagid = tagid AND t_stamp >= :datee AND t_stamp <= :datee2 ORDER BY t_stamp DESC ) AS EndingState FROM sqlt_data_1_2022_06 AS a WHERE a.t_stamp >= :datee AND a.t_stamp <= :datee2 and tagid = 4) AS d GROUP BY d.tagid, d.EndingState, d.StartingState;
(This query is completely broken it gives crazy answers and i cannot figure i out)