I am trying to get value at 06:00 AM for multiple tags, here I need if vale not existing at 06:00 AM then previous value using PI SQL commander.
Please suggest me with example.. Thanks in advance.
SELECT tag, time, value FROM piinterp WHERE time = 't+6h' AND tag in ('SINUSOID', 'SINUSOIDU', 'CDT158')
is probably not what you want but could work if the points would have the step attribute set to 1. You may want to adjust the timestamp passed with the following query slightly to ensure you include events at 6 AM.
SELECT tag, prevval(tag, time) beforeval, prevevent(tag, time) beforetime FROM piinterp WHERE time = 't+6h' AND tag in ('SINUSOID', 'SINUSOIDU', 'CDT158')
I found this query example in the Pi SQL Commander Query Compendium
-- Finds the nearest "sinusoid" event to the today's midnight.
-- The "... FROM (SELECT 'sinusoid' mytag, DATE('t') mytime) p ..." part of the
-- statement defines query parameters - tag and time. As you can see here,
-- SQL CASE operator allows you to incorporate logic into SQL queries so that
-- for simple algorithms, you can use SQL instead of programming.
SELECT CASE WHEN (mytime - prevtime) < (nexttime - mytime) THEN prevtime ELSE nexttime END
SELECT TOP 1 time
WHERE tag = p.mytag AND time <= p.mytime
ORDER BY tag, time DESC
WHERE tag = p.mytag AND time >= p.mytime
ORDER BY tag, time ASC
SELECT 'sinusoid' mytag, DATE('t') mytime
Hope it helps.
Thanks for the response, this query similar but this gives only one tag vale also in code selected top 1 row.
I will try to modify this..
Any suggestion on this please welcome... Thank you
Retrieving data ...