I was wondering if there is a simple way to get the very first measured value of a certain tag.Is it posible?
Any help would be great!
Did you already try
select top 2 * from picomp2 where tag='sinusoid' and time > '1-Jan-1980' order by tag, time ASC
This should return a 'Pt Created' and the next one, hopefully in reasonable time.Finetuning, by then filtering the first good value only, is not very PI OLEDB specific any more.
First of all, thank you very much for your reply.
So the second one is the first measured value of tag, being this one bad or not?
When you create a PI Tag, the first event that is going to the archive is "Pt Created" indicating when the tag was created. With regards to process data / recording, I would consider "Pt Created" a bad status. Sometimes you may not find a "Pt Created" event because it has been deleted by someone.
With recorded values, a bad status is indicated by a negative number within the status column. "Good" values show status = 0.
You can translate the negative status numbers into their representation within SYSTEM Digital State set using the DigString() function.
select top 2 tag, time, value, DigString(status) as state from picomp2 where tag='sinusoid' and time > '01-Jan-1970' order by tag, time ASC
"a simple way to get the very first measured value of a certain tag"
raises the question "Why do you want to know this"Perhaps your question really meant "When was the this tag created " ...
Depending on how you put the point into service, the first values are perhaps nonsense, or - as Gregor mentionend - especially if the data comes from some backfilling, the very first event might not be the standard "Pt Created" status.
The other thing is that the "top x" clause can only work well, if you do not apply too many filter criteria, and don't omit the "order by tag". Otherwise the provider would need to first get "the whole" archive, flter, and then do the "top 1". Which won't work with big archives.
This is why I did not suggestselect top 1 * from picomp2 where tag='sinusoid' and time > '1-Jan-1970' and status = 0 order by tag, time ASC -- might not work
Feel free to use my first proposal and filter the result in a wrapping query according to your needs, but you asked for "a simple way" ;)
select top 1 time "Sinusoid's first good value at", value from (select top 5 time, status, value from picomp2 where tag = 'sinusoid' and time > '1-jan-1970' order by tag, time ASC) datawhere data.status = 0 and data.value > 0
This would allow for 0 to 4 bad status and zero value events, before real measurement started.
Retrieving data ...