6 Replies Latest reply on Dec 23, 2016 1:55 PM by gregor

    SQL query for Reletional Datasets

    igreguri

      Hi,
      I have problems with creating more complex query for Reletional Datasets.
      I want to compare one set of PI points with other (density for storage tanks).

      One set is naming like 'RT%D15.PV', and other 'RT%.D15.LA'
      Values must be the same, but in some cases there is a difference.
      I want to see on which pair of tags is difference in value or timestamp.


      Here is my example, but I would different...

      SELECT s.tag,d.descriptor, value, time, case when status = 0 THEN 'OK' ELSE 'BAD' END as status
      FROM [piarchive].[pisnapshot] s
      inner JOIN [pipoint].[classic] d ON s.tag = d.tag
      WHERE s.tag LIKE 'RT%D15.PV' or s.tag LIKE 'RT%.D15.LA'

      In this case, columns in table are:
      tag
      value
      time
      status

      How to create query with this columns in table:
      tag1 ('RT%D15.PV' )
      value1
      time1
      tag2 ('RT%.D15.LA')
      value2
      time2
      status (“BAD” if value1 <> value2 or “OK” if value1 = value2)

      Regards,
      Igor

        • Re: SQL query for Reletional Datasets
          gregor

          Hello Igor,

           

          I thought this would be an easy one but quickly found that the comparison of values to evaluate the status makes it quite complex. My good colleague Rene Otisk, however knows about all the powerful tricks with SQL and tackled your challenge easily. Thank you Rene!!

           

          SELECT tag1, time1, value1, tag2, time2, value2, CASE WHEN value1 = value2 THEN 'OK' ELSE 'BAD' END status
          FROM
          (
              SELECT tag tag1,
                  time time1,
                  value value1,
                  tagname tag2,
                  (SELECT time FROM piarchive..pisnapshot WHERE tag = tagname) time2,
                  (SELECT value FROM piarchive..pisnapshot WHERE tag = tagname) value2
              FROM
              (
                  SELECT 'SINUSOIDU' tagname
              ) table1,
              piarchive..pisnapshot
              WHERE tag = 'SINUSOID'
          ) table2