WonderWare ActiveFactory Query - SQL Custom Retrieval Help

chrisj91

Member
Join Date
Nov 2010
Location
England
Posts
33
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

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.
 
Resolved

Took a bit of extra effort but I managed to sort this out. I predicted that it was going to be a bit easier than what I ended up doing but hey ho...

I ended up using SQL Enterprise's Query Analyzer instead of WonderWare's - went direct to the source!
I had to create a table for each of the tags, then another containing all the DateTime values from the 2 new tables, then the results could be displayed by 'joining' the columns from each tag table onto the newly created joint DateTime table.

For anyone interested code is below:
(Took me a bit of time to figure it out and may not be the tidiest solution but I don't have any experience with SQL Queries and it works so I'm happy!)

Code:
SET QUOTED_IDENTIFIER OFF

DROP TABLE ACOQ1
DROP TABLE ACOQ2
DROP TABLE JointDateTime

CREATE TABLE ACOQ1
(
DateTime CHAR(50),
ACOQ1_NO_1_PUMP_RUNNING INT,
CONSTRAINT [pk_Key1] PRIMARY KEY (DateTime)
);

INSERT IGNORE INTO ACOQ1 (DateTime, ACOQ1_NO_1_PUMP_RUNNING)
SELECT * FROM OpenQuery(INSQL, "SELECT DateTime = convert(nvarchar, DateTime, 113),  [ACOQ1_NO_1_PUMP_RUNNING]
 FROM Runtime.dbo.WideHistory
 WHERE [ACOQ1_NO_1_PUMP_RUNNING] = 1
AND wwVersion = 'Latest'
 AND wwRetrievalMode = 'Delta'
 AND DateTime >= '20150501 00:00:00.000'
 AND DateTime <= '20150511 11:59:59.000'")

CREATE TABLE ACOQ2
(
DateTime CHAR(50),
ACOQ2_NO_1_PUMP_RUNNING INT,
CONSTRAINT [pk_Key2] PRIMARY KEY (DateTime)
);

INSERT IGNORE INTO ACOQ2 (DateTime, ACOQ2_NO_1_PUMP_RUNNING)
SELECT * FROM OpenQuery(INSQL, "SELECT DateTime = convert(nvarchar, DateTime, 113),  [ACOQ2_NO_1_PUMP_RUNNING]
 FROM Runtime.dbo.WideHistory
 WHERE [ACOQ2_NO_1_PUMP_RUNNING] = 1
AND wwVersion = 'Latest'
 AND wwRetrievalMode = 'Delta'
 AND DateTime >= '20150501 00:00:00.000'
 AND DateTime <= '20150511 11:59:59.000'")


CREATE TABLE JointDateTime
(
DateTime CHAR(50)
CONSTRAINT [pk_Key3] PRIMARY KEY (DateTime)
);

INSERT IGNORE INTO JointDateTime (DateTime)
SELECT ACOQ1.DateTime FROM ACOQ1
UNION
SELECT ACOQ2.DateTime FROM ACOQ2

SELECT JointDateTime.DateTime, ACOQ1.ACOQ1_NO_1_PUMP_RUNNING, ACOQ2.ACOQ2_NO_1_PUMP_RUNNING
FROM (SELECT ACOQ1.DateTime FROM ACOQ1 UNION
SELECT ACOQ2.DateTime FROM ACOQ2)
JointDateTime
LEFT OUTER JOIN ACOQ1
ON JointDateTime.DateTime=ACOQ1.DateTime
LEFT OUTER JOIN ACOQ2
ON JointDateTime.DateTime=ACOQ2.DateTime

PS Sorry, I know this all a little off topic from main PLC/HMI/SCADA work but I thought it was initially going to be related by using WonderWare's ActiveFactory etc.
 
I wish I knew SQL as well as you! I had a similar problem http://www.plctalk.net/qanda/showthread.php?t=93878 that I found was related to my WW Update Interval (500 ms) seemed to be faster than the historian was retrieving the values. In my situation, an array of 5 values were being triggered at the same time, but the historian would create two rows during the retrieval, with half being inaccurate.

I was able to set up a new Scan Group set at two seconds, which seems to have allowed the change to update and eliminated the "Double Scans" during the data change. Now, using the delta retrieval mode, I get a clean display of data. I'm not sure if it's right, but it's working for now.

I actually logged on today to mention this in my past post to give some type of resolution, because it seems to be working well, but I noticed your post, and it seems to be somewhat related. Thanks for sharing your solution!
 

Similar Topics

Hi, A customer of mine has ActiveFactory Trend for viewing our historical data. Appears to be version 9.1.….. Sorry if these questions are very...
Replies
1
Views
5,270
Hello 1st post so sorry if i am in the wrong place I am trying to connect to a machine running Insql 8. I add the server info in the server...
Replies
3
Views
5,382
Hi, We are setting up an Aveva Plant SCADA node with the intention to connect it to a Wonderware Historian node. Everywhere I look online I see...
Replies
1
Views
120
Hola chicos. Tengo un problema con el driver de comucicacion dasabcip 5, y un plc controllogix v34, ya realice la comunicacion pero en ciertos...
Replies
2
Views
103
Hi, I am upgrading a Wonderware SCADA form version 9.5 to version 23. I am able to migrate all the graphic, but when to activate the runtime this...
Replies
8
Views
339
Back
Top Bottom