1 Reply Latest reply on Dec 5, 2013 7:33 PM by Raj Jamadagni

    different column value with same effdt

    Kki

      Hi All,

       

      I have to table like below records

       

      Select * from pa_tbl where emplid=’ 5705838’

      Gives the Below result.

       

      EMPLID

      COMPANY

      EFFDT

      STATE_CODE

      RESIDENT

      NON_RES

      5705838

      T01

      8/20/1998

      CO

      N

      N

      5705838

      T01

      8/20/1998

      PA

      Y

      N

      5705838

      T01

      1/1/2001

      CO

      N

      N

      5705838

      T01

      1/1/2001

      PA

      Y

      N

      From above result I have to select  only ‘state_code’

      1. 1.Condition is  resident is ‘N’ with max(effdt)

      I have to select state code like ‘AK’, ‘NV’, ‘CO’, ‘PR’ among lot of state_code .

      Above example only for CO state_code

       

      I am able to extract sate code like  ’CO’ using below query

      SELECT  STATE

                FROM PS pa_tbl S

               WHERE S.EMPLID  ='5705838'

                 AND S.RESIDENT = 'N'

                 AND S.STATE IN('AK','NV','CO','MI')

                 AND S.EFFDT   = (SELECT MAX(EFFDT)

                                FROM PS pa_tbl

                               WHERE EMPLID  = S.EMPLID

                                 AND COMPANY = S.COMPANY

                                 AND EFFDT <=SYSDATE);

      Problem:-

      Here user entered 1/1/2001 state_code is PA and resident=Y which is latest. In this case my query should return NULL/NODATA found.

       

      But my query returns STATE_CODE=CO (this is wrong)

       

      How to write a sql query?

       

      Thanks,

      Krupa