1 Reply Latest reply: Nov 30, 2015 4:48 AM by Avinash R RSS

    SQL Statement Error

      I want to group by all the primary key and select the largest effectivity year per PIN. Here's my example.

       

      PIN    OwnerNum Effectivity_Year Total Tax
      pin123 1003     2014             2,000.00
      pin123 1002     2009             3,000.00
      pin456 4002     2015             1,500.00
      pin456 4001     2014               900.00

       

      And My desired output in sql query

       

      PIN    OwnerNum  Effectivity Year Total Tax
      pin123 1003      2014             2,000.00
      pin456 4002      2015             1,500.00

       

       

      I really find this hard because i'm conducting BI report for Real Property Tax System that has no future or past entities for effectivity year.To simplify the data, I created a module whose effectivity year is <=2015. I want to fetch the highest effectivity year only per pin and owner.Thanks for all your help.


      Here's my script


      [past_or_present effyr]:

       

      LOAD PCINum,

          MDINum,

          BINum,

          SINum,

          Parcel,

          PType,

          OwnerNum,

        Date(Max(yr)) as pyear,

          PCINum&'-'&MDINum&'-'&BINum&'-'&SINum&'-'&Parcel&'-'&PType as Pin3;

      SQL SELECT PCINum,

          MDINum,

          BINum,

          SINum,

          Parcel,

          PType,

          OwnerNum,

          yr

      FROM PROP.PUB.Property

      WHERE yr <= 2015 Group By PCINum,

          MDINum,

          BINum,

          SINum,

          Parcel,

          PType;

       

      I try to use this query bu t it has some errors.

        • Re: SQL Statement Error
          Avinash R

          try like this :

           

          [past_or_present effyr]:

           

          LOAD PCINum,

              MDINum,

              BINum,

              SINum,

              Parcel,

              PType,

              OwnerNum,

            yr as pyear,

              PCINum&'-'&MDINum&'-'&BINum&'-'&SINum&'-'&Parcel&'-'&PType as Pin3;

          SQL SELECT PCINum,

              MDINum,

              BINum,

              SINum,

              Parcel,

              PType,

              OwnerNum,

              yr

          FROM PROP.PUB.Property

          WHERE yr <= 2015 Group By PCINum,

              MDINum,

              BINum,

              SINum,

              Parcel,

              PType;

           

           

           

           

           

          Result_table:

           

          LOAD

          max(OwnerNum ) as New_OwnerNum ,

          PIN as NEW_PIN,

          Effectivity_Year as NEW_Effectivity_Year,

          [Total Tax] as NEW_Total_Tax

          Resident

          [past_or_present effyr]

          group by

          PIN,

          Effectivity_Year,

          Total Tax;



          Resultant table will give you the result