I have not known how to find the answer, so my apologies if it was already answered.I need to obtain the average, the maximum and the minimum of two tags together, as if it were just one, is that possible? If so, how?
Thanks in advance.
Sometimes there is nothing better than to write to find the solution to a problem ;-)I have come up with a solution, I do not know if it will be the right one, but I think it can be worth.
An SQL query using PI Oledb with a subquery that includes two "joined" queries it gives me the solution
SELECT Avg(Value) AS Media, Max(Value) AS Maximo, Min(Value) AS Minimo
SELECT time, value FROM piarchive..picomp WHERE Tag = 'FRH:A_055' AND time BETWEEN 'y+6h' AND 't+6h'
SELECT time, value FROM piarchive..picomp WHERE Tag = 'FRH:A_038' AND time BETWEEN 'y+6h' AND 't+6h'
) AS Consulta
Of course if someone thinks there is a better solution I am open to suggestions
Please provide more details on your use case before we can give you the best recommendation.
Do you want this done one time, or continuously? Do you want to use a tool based on configuration only or a tool that requires you to write some code? How much data are we talking about - 1 value, 1 min, 1 day, 1 week, 1 month, 1 year? Your example above is for I believe 24 hours. How will you use the results? Do you need to store the results into another PI Point for trending or other use case?
Thanks for your answer StephenThe data will be part of a daily report made in Excel through VBA, each of them will go to a cell of the same and effectively refer to the previous day.
This report is automatically executed every morning at the beginning of a new day.
It looks like a daily report.
You could do it with AF Analytics Event Frames, aggregate what you need in the Event Frame, and then pull the Event Frames into Excel via PI DataLink.
That way nobody needs to maintain the SQL, other users can make use of the Event Frames...and you can quickly expand the Event Frames going forward.
Thank you Rhys,
unfortunately, for reasons that are not relevant, we are not working yet with AF, so I can not do it as you suggest.
In principle I don't think there is any problem with the SQL, once written in the code I do not see the need for any maintenance.
Understood. No AF obviously rules out Event Frames.
You are probably good to go with what you have then.
Then you can think about the AF install...
I've been waiting for years to use AF ...I'll be out a few days, apologize if I do not answer.
When you return, you may want to consider Building your report using PI DataLink. Even you don't use Asset Framework yet PI DataLink is the tool designed to build reports in Microsoft Excel. What you like to accomplish can easily be done using PI DataLink without writing a single line of code or a SQL query - just by configuration.
Thanks Gregor,Of course we know that Datalink is the right tool, however many of our reports are used by people who do not have it installed, so we prefer to build them with VBA and PISDK that allow us to write values without relying on Datalink.
I understand the dilemma but I disagree with the conclusion.
For sure there is cost involved with buying product licenses and paying the annual SRP fee but there is also money involved with custom development, with maintaining and supporting custom products. Assuming required resources are employed and hence the costs are paid anyhow, is a wrong assumption from the economical standpoint. Re-inventing something often means to spent way more money compared to buying an existing solution. When starting to build a custom solution, you often don't have its final specifications and you must expect users to ask more.
PI DataLink is a mature piece of software, actively supported by OSIsoft. My suggestion is to qualify the demand by counting how many more licenses you need and to reach out to your account manager at OSIsoft.
Thanks again Gregor.
I am not completely in agreement with the philosophy either, in fact it is not a problem of licenses, we have them unlimited, but this is what the direction has decided and I can only move forward.
This is not even a question of license and service cost? Wow! That's .. let me be careful .. "interesting".
Retrieving data ...