mhamel

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 Employee 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

 

 

Outcomes