Hi all,
Having a little trouble getting the result I require, I'm close but no cigar.
Basically, I am wanting to display a table within the Query Analyser (part of WonderWare's ActiveFactory - 9.2 I believe). The table will show a few discrete tags on a Delta retrieval method against just the one DateTime column.
My issue is getting it to just display a row whenever one of the tags has become True.
I say I'm close because I've sort of got this working, problem is with my method - whenever one of the tags becomes False whilst another is still True, it logs this as a new row. I only want a new row to appear whenever a tag becomes True.
To explain graphically. . .
Current Set Up
¦ Date Time ¦ Tag_1 ¦ Tag_2 ¦
----------------------------------
¦_00:07:15_¦___0___¦__1___¦ --> Tag 2 became active whilst Tag 1 was off - fine
¦_00:16:13_¦___1___¦__1___¦ --> Tag 1 became active whilst Tag 2 was on - fine
¦_00:25:00_¦___0___¦__1___¦ --> Tag 1 became inactive whilst Tag 2 still on - don't want to see this row
¦_02:45:09_¦___1___¦__1___¦ --> Tag 1 came back on whilst Tag 2 still on - fine
My SQL Code
I double checked against the true Historical Data against both tags - Tag 1 reflected that of the table above on a normal Delta retrieval (including 0s) and Tag 2 remained on the whole time (never changed from a 1 to a 0).
Could someone point me in the right direction?
I should also mention that I have only got it set up on these two tags so far but the table will eventually consist of at least 10 tags so it does need to work with more than just two.
Having a little trouble getting the result I require, I'm close but no cigar.
Basically, I am wanting to display a table within the Query Analyser (part of WonderWare's ActiveFactory - 9.2 I believe). The table will show a few discrete tags on a Delta retrieval method against just the one DateTime column.
My issue is getting it to just display a row whenever one of the tags has become True.
I say I'm close because I've sort of got this working, problem is with my method - whenever one of the tags becomes False whilst another is still True, it logs this as a new row. I only want a new row to appear whenever a tag becomes True.
To explain graphically. . .
Current Set Up
¦ Date Time ¦ Tag_1 ¦ Tag_2 ¦
----------------------------------
¦_00:07:15_¦___0___¦__1___¦ --> Tag 2 became active whilst Tag 1 was off - fine
¦_00:16:13_¦___1___¦__1___¦ --> Tag 1 became active whilst Tag 2 was on - fine
¦_00:25:00_¦___0___¦__1___¦ --> Tag 1 became inactive whilst Tag 2 still on - don't want to see this row
¦_02:45:09_¦___1___¦__1___¦ --> Tag 1 came back on whilst Tag 2 still on - fine
My SQL Code
Code:
SET QUOTED_IDENTIFIER OFF
SELECT * FROM OpenQuery(INSQL, "SELECT DateTime = convert(nvarchar, DateTime, 113), [Tag_1], [Tag_2]
FROM Runtime.dbo.WideHistory
WHERE [Tag_1] = 1 OR [Tag_2] = 1
AND wwVersion = 'Latest'
AND wwRetrievalMode = 'Delta'
AND DateTime >= '20150501 00:00:00.000'
AND DateTime <= '20150511 11:59:59.000'")
ORDER BY DateTime ASC
I double checked against the true Historical Data against both tags - Tag 1 reflected that of the table above on a normal Delta retrieval (including 0s) and Tag 2 remained on the whole time (never changed from a 1 to a 0).
Could someone point me in the right direction?
I should also mention that I have only got it set up on these two tags so far but the table will eventually consist of at least 10 tags so it does need to work with more than just two.