11 Replies Latest reply on Mar 13, 2018 8:18 AM by igreguri

    SQL to sumarize same materials in tanks

    igreguri

      SQL to sumarize same materials in tanks

      I would like by SQL query from PI database to make a overview amount (sum) of crude material in crude storages, total amount in tonns by crude name.

      We have 7 crude storages, and process a lot of different raw materials. For that reason we want to know how much we have current actual raw materials.

       

      PI Tags:

      Quantities in tanks

      Materials in tanks

      RTA016MI.PV

      RTA016.MATERIAL.MDE

      RTA018MI.PV

      RTA018.MATERIAL.MDE

      RTA019MI.PV

      RTA019.MATERIAL.MDE

      RTA020MI.PV

      RTA020.MATERIAL.MDE

      RTA021MI.PV

      RTA021.MATERIAL.MDE

      RTA022MI.PV

      RTA022.MATERIAL.MDE

      RTA023MI.PV

      RTA023.MATERIAL.MDE

       

      Overview in AF:

      pic1.jpg

       

      Example (picture above):

      In this case I want to know...

      Crude                                           SUM (t)

      SIR_AZERI_LIGHT                        ?

      SIR_BASRAH_LIGHT                    ?             

      SIR_MIX                                         ?

       

      How to create SQL query for this?

      Thanks,

      Igor

        • Re: SQL to sumarize same materials in tanks
          rschmitz

          Hi Igor,

           

          You can utilize PI OLEDB Enterprise to query the data from PI AF via SQL query. Included with the software is query compendium with pre-built basic SQL queries to help get you started with this process.

           

          Cheers,

          Rob

            • Re: SQL to sumarize same materials in tanks
              igreguri

              I extracted and successfully COUNT data, but when I try to SUM data I get an error message:

              [SUM] Overload resolution failed for (Variant) argument(s). 

               

              COUNT:

              pic2.jpg

               

              SUM:

              SELECT

                   value as Material,

                   --COUNT(value1) as Number

                   SUM(value1) as Mass

                   FROM

              ( 

                   SELECT

                          s1.tag tag1,  

                          s1.value value1,

                             LEFT(s1.tag, 6) + '.MATERIAL.MDE' tag2

                   FROM piarchive..pisnapshot s1            

                   WHERE s1.tag LIKE 'RTA016MI.PV' or s1.tag LIKE 'RTA018MI.PV' or s1.tag LIKE 'RTA019MI.PV' or s1.tag LIKE 'RTA020MI.PV' or s1.tag LIKE 'RTA021MI.PV' or s1.tag LIKE 'RTA022MI.PV' or s1.tag LIKE 'RTA023MI.PV'

              ) table1 

              INNER JOIN piarchive..pisnapshot ON tag = tag2     

              GROUP BY value

              OPTION (FORCE ORDER) 

                • Re: SQL to sumarize same materials in tanks
                  igreguri

                  I find solution... cast to float32.

                  SELECT

                       value as Material,

                       SUM(cast(value1 as float32)) as Mass

                       FROM

                  ( 

                       SELECT

                              s1.tag tag1,  

                              s1.value value1,

                                 LEFT(s1.tag, 6) + '.MATERIAL.MDE' tag2

                       FROM piarchive..pisnapshot s1            

                       WHERE s1.tag LIKE 'RTA016MI.PV' or s1.tag LIKE 'RTA018MI.PV' or s1.tag LIKE 'RTA019MI.PV' or s1.tag LIKE 'RTA020MI.PV' or s1.tag LIKE 'RTA021MI.PV' or s1.tag LIKE 'RTA022MI.PV' or s1.tag LIKE 'RTA023MI.PV'

                  ) table1 

                  INNER JOIN piarchive..pisnapshot ON tag = tag2     

                  GROUP BY value

                  OPTION (FORCE ORDER) 

                   

                  pic3.jpg

                  1 of 1 people found this helpful
              • Re: SQL to sumarize same materials in tanks
                vkaufmann

                Seems like the easier solution here would be to configure a rollup analysis to calculate the sum but glad you got your sql query working. If you then wish to use this sum downstream somewhere the query would be trivial in OLEDB Enterprise or Classic provider.

                 

                --Vince

                1 of 1 people found this helpful