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)