3 Replies Latest reply on Mar 21, 2018 8:23 AM by igreguri

    Questionable values longer then 1 hour

    igreguri

      I've created a query for list questionable data...

      I want to show only points with data in questionable status continuously for more than an hour.

      How to do this?

       

      SELECT tag, time
      FROM [piarchive]..[picomp2]
      WHERE tag like '*.PV' and Questionable = True and time >= '*-1h'
      --GROUP BY tag
      OPTION (FORCE ORDER)

       

      Questionable data longer then 1 hour.jpg

        • Re: Questionable values longer then 1 hour
          jyi

          Hi Igor,

           

          Our SQL Compendium show below query. Please try adjusting below query:

          SELECT tag FROM piarchive..pisnapshot
          WHERE tag IN (
          
             WHERE pointsource = 'C')
          
          
          AND ((DATE('*') - time) BETWEEN RELDATE('1h') AND RELDATE('365d') OR status <> 0)
          

           

           

          We have many other useful example queries in compendium as well. Please take a look!

          Compendium of PI SQL statements - Archive - Raw Archive Data

          3 of 3 people found this helpful
          • Re: Questionable values longer then 1 hour
            jyi

            Hi Igor,

             

            If you found above reply to be helpful and have no further questions, then please take my reply as 'Correct Answer'!

             

            Thank you,

            Jin

              • Re: Questionable values longer then 1 hour
                igreguri

                Hi Yi,

                I have not been able to make a query as I wanted...

                I get bad and stale data from snapshoot (as in the example), but when I try to show only points with data in questionable status continuously for more than an hour (from picomp table ) and group this by tag does not go.

                 

                I try this, but here is not working GROUP BY...

                SELECT tag, format (time,'dd.MM.yyyy  HH:mm:ss') as time
                FROM [piarchive]..[picomp]
                WHERE tag like '*.PV' and time >= '*-1h' and flags='Q'
                OPTION (FORCE ORDER)
                ...or...
                SELECT tag, format (time,'dd.MM.yyyy  HH:mm:ss') as time
                FROM [piarchive]..[picomp2]
                WHERE tag like '*.PV' and Questionable = True and time >= '*-1h'
                OPTION (FORCE ORDER)

                 

                So, the goal is to keep track of tags that are full hour of time in questionable status (grouped by tags).