• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1096
  • Last Modified:

How to SELECT event-based values from a time-based table?

Experts,

I need to get some values from our PI historian database. The database table is time based; but I need to display the values of a batch process. Thus the values doesn't have to show up on a time interval basis, every hour for example, but based on an event. When a predefined boolean flag is up, I need all the tags values that have been collected at that time. The table fields are: tag, time, value. The query result should be like this:

Tag_      time_____________      value_
Flag      06/4/2011 00:05:30      1
Tag1      06/4/2011 00:05:30      32
Tag2      06/4/2011 00:05:30      7.7
Flag      06/4/2011 01:37:12      1
Tag1      06/4/2011 01:37:12      35
Tag2      06/4/2011 01:37:12      6.9
And so on...

Here is the incomplete query I’ve built so far. It only manages to get the good flag values, but the other values are still missing:

SELECT tag, time, value
FROM PI.piarchive..picomp2
WHERE (time >= @time)
AND (time < @time + 1)
AND (tag = @flag)
AND (value = 1)

And here is another query I’m using to get the time-based values. This one is working fine and it might help:

SELECT tag, time, value
FROM PI.piarchive..picomp2
WHERE tag IN(SELECT tag FROM PI.pipoint..classic2
             WHERE (group = 1)
             AND (tag LIKE '206%'))
AND time >= '4/6/2011' AND time < '4/7/2011'
ORDER BY time, tag

Note the second table “PI.pipoint..classic2” which contains the tag definitions. It is needed because of the “group” column which restricts the query results to tags from a defined group.

Thanks
0
Martin Beausoleil
Asked:
Martin Beausoleil
  • 3
  • 3
1 Solution
 
Martin BeausoleilAuthor Commented:
I thought that a query like this would have worked but no:

SELECT tag, time, value
FROM PI.piarchive..picomp2
WHERE tag IN(SELECT tag FROM PI.pipoint..classic2
             WHERE (groupe = 1)
             AND (tag LIKE '206%'))
AND time IN(SELECT time
            FROM PI.piarchive..picomp2
            WHERE (time >= '4/6/2011')
            AND (time < '4/6/2011 02:00:00')
            AND (tag = '206-525-CBR-545.YI-New Batch.DIG')
            AND (value = 1))
ORDER BY time, tag

(Note that I've filled the parameter slots with real values for the testing purpose)

There’s an error message:  
Failed to retrieve events from server.  [-11091] Event collection exceeded the maximum allowed]
0
 
SharathData EngineerCommented:
Can you provide some sample data with expected result?
0
 
Martin BeausoleilAuthor Commented:
I've copied some sample data in an Excel spreadsheet. The "New Batch" tag is the flag in this case. When it's at 1 we want to get the values of the other tags at the same time.
Batch-sample-results.xlsx
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
SharathData EngineerCommented:
Can you check this?
SELECT p.tag,p.TIME,p.VALUE 
    FROM PI.piarchive..picomp2 p 
         JOIN (SELECT TIME 
                 FROM PI.piarchive..picomp2 
                WHERE TIME >= '4/6/2011' 
                      AND TIME < '4/6/2011 02:00:00' 
                      AND tag = '206-525-CBR-545.YI-New Batch.DIG' 
                      AND VALUE = 1) AS t1 
           ON p.TIME = t1.TIME 
   WHERE p.tag IN (SELECT tag 
                     FROM PI.pipoint..classic2 
                    WHERE (groupe = 1) 
                          AND (tag LIKE '206%')) 
ORDER BY p.TIME,p.tag

Open in new window

0
 
Martin BeausoleilAuthor Commented:
It's working great! And the execution is really quick.

Thank you so much!

(Sorry for the delay, I'm not at the office today.)
0
 
SharathData EngineerCommented:
Glad that I could help you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now