# Interesting query: How to sample data for a specific tag and show results only if no questionable events have occurred during the sample period

Discussion created by mhamel on Feb 20, 2009
Latest reply on Jul 24, 2009 by cescamilla

(Warning) This material may be difficult to understand for beginners.
If you are interested to get sampled data for a specific tag only when it did not occur any questionable events during the sample period, you can use the query presented below.  Some explanations follow the query.

SELECT
T4.TAG,
T4.TIME,
T4.VALUE
FROM
(SELECT
T3.TAG,
T3.TIME,
MIN(CAST(T3.VALUE AS FLOAT32)) AS VALUE,
SUM(CASE WHEN T3.QUESTIONABLE = -1 THEN 1 ELSE 0 END) AS ISQUESTIONABLE
FROM
(SELECT T1.TAG, T1.TIME2 AS TIME, T1.VALUE, T2.QUESTIONABLE
FROM
(SELECT TAG, TIME - TIME('1h') AS TIME1, TIME AS TIME2, VALUE
FROM PIINTERP2
WHERE TAG = 'cdt158' AND TIMESTEP = '1h'
AND TIME BETWEEN 'y' AND 't') T1

INNER JOIN

(SELECT TAG, TIME, QUESTIONABLE
FROM PICOMP2
WHERE TAG = 'cdt158'
AND TIME BETWEEN 'y-1h' AND 't') T2

ON T2.TAG = T1.TAG AND (T2.TIME BETWEEN T1.TIME1 AND T1.TIME2)) T3
GROUP BY T3.TAG, T3.TIME) T4
WHERE T4.ISQUESTIONABLE = 0

Aliases (T1, T2, T3 and T4) serve only the purpose of making the query clearer and more manageable. The inner query calling the PIINTERP2 table collects the sample data that you want. The other inner query calling the PICOMP2 table collects all events during that same period with the possible questionable flag. Then a join will be made on both result set of these two inner queries. Then a grouping operation will be performed to get the tag, time, value and the number of possible events with the questionable flag set to true. Finally, the last part is to filter out any sample data that have encountered at least one questionable event.

You can vary this query to filter sample data whenever a digital state was written during the sample period.

SELECT
T4.TAG,
T4.TIME,
T4.VALUE
FROM
(SELECT
T3.TAG,
T3.TIME,
MIN(CAST(T3.VALUE AS FLOAT32)) AS VALUE,
SUM(CASE WHEN T3.STATUS <> 0 THEN 1 ELSE 0 END) AS NOTAVALUE
FROM
(SELECT T1.TAG, T1.TIME2 AS TIME, T1.VALUE, T2.STATUS
FROM
(SELECT TAG, TIME - TIME('1h') AS TIME1, TIME AS TIME2, VALUE
FROM PIINTERP2
WHERE TAG = 'cdt158' AND TIMESTEP = '1h'
AND TIME BETWEEN 'y' AND 't') T1

INNER JOIN

(SELECT TAG, TIME, STATUS
FROM PICOMP2
WHERE TAG = 'cdt158'
AND TIME BETWEEN 'y-1h' AND 't') T2

ON T2.TAG = T1.TAG AND (T2.TIME BETWEEN T1.TIME1 AND T1.TIME2)) T3
GROUP BY T3.TAG, T3.TIME) T4
WHERE T4.NOTAVALUE = 0

Finally, you can combine the idea of the two queries.

SELECT
T4.TAG,
T4.TIME,
T4.VALUE
FROM
(SELECT
T3.TAG,
T3.TIME,
MIN(CAST(T3.VALUE AS FLOAT32)) AS VALUE,
SUM(CASE WHEN T3.STATUS <> 0 THEN 1 WHEN T3.QUESTIONABLE = 1 THEN 1 ELSE 0 END) AS NOTAVALUE
FROM
(SELECT T1.TAG, T1.TIME2 AS TIME, T1.VALUE, T2.STATUS, T2.QUESTIONABLE
FROM
(SELECT TAG, TIME - TIME('1h') AS TIME1, TIME AS TIME2, VALUE
FROM PIINTERP2
WHERE TAG = 'cdt158' AND TIMESTEP = '1h'
AND TIME BETWEEN 'y' AND 't') T1

INNER JOIN

(SELECT TAG, TIME, STATUS, QUESTIONABLE
FROM PICOMP2
WHERE TAG = 'cdt158'
AND TIME BETWEEN 'y-1h' AND 't') T2

ON T2.TAG = T1.TAG AND (T2.TIME BETWEEN T1.TIME1 AND T1.TIME2)) T3
GROUP BY T3.TAG, T3.TIME) T4
WHERE T4.NOTAVALUE = 0