I have a SQL table of alarms for some of our mobile fleet that I am trying to capture using a PI-RDBMS interface. We are looking to track durations as well as monitor when alarms have been active beyond a certain time limit as the existing application does not provide this information.
The interface is configured and working as I have been able to retrieve values for other telemetry successfully.
The table I am now working with is a little different in that it has 2 timestamp columns to work with - START_TIMESTAMP and END_TIMESTAMP
Each alarm will be written to its own Pi Tag, which will be identified by EQUIP_IDENT which is the vehicle and the ALARM_PROTOCOL_IDENT which identifies which alarm it is. For example in line 1 Pi Tag could be EX009.EngineOverspeedAlarm.
What I need to achieve is have the SQL statement write to the PI Tag: "ON" with the START_TIMESTAMP" and "OFF" with the END_TIMESTAMP.
I can do a single statement to write a single value, for example:
/SQL="SELECT START_TIMESTAMP, 'ON' AS PI_VALUE1,0 FROM ALARM_TRANS WHERE EQUIP_IDENT='EX002' AND ALARM_PROTOCOL_IDENT='10651' AND START_TIMESTAMP > ? ORDER BY START_TIMESTAMP ASC;" P1=TS
I am getting stuck on the syntax for the above for multiple values to the same tag (I am by no means an expert in SQL if that is not already obvious)
I have tried the following but it just keeps writing the last ON value only repetitively to PI with the same timestamp and does not write any OFF value:
/SQL="SELECT START_TIMESTAMP, 'ON' AS PI_VALUE1,0, END_TIMESTAMP, 'OFF' AS PI_VALUE2,0 FROM ALARM_TRANS WHERE EQUIP_IDENT='EX002' AND ALARM_PROTOCOL_IDENT='10651' AND START_TIMESTAMP > '01-DEC-2018' ORDER BY START_TIMESTAMP ASC;" P1=TS
I would appreciate any input to help get this working.
Thanks in advance