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
Martin BeausoleilAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.