Reply from Richard on Apr 10 at 11:13 AM Assuming you are using Oracle, another way to do it is to use the RANK feature. I use it to grab the most recent effective date but it is trivial to get the second or third or whatever date. It is also very efficient and fast compared to using a subselect. For example (this incorporates EFFDT and EFFSEQ but changing for just EFFDT is easy) SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ FROM ( SELECT B.EMPLID , B.EMPL_RCD , B.EFFDT , B.EFFSEQ , RANK () OVER (PARTITION BY EMPLID , EMPL_RCD ORDER BY EFFDT DESC , EFFSEQ DESC) RNK FROM PS_JOB B WHERE B.EFFDT <= %CurrentDateIn) A WHERE RNK = 1 Change RNK=2 to retrieve the previous effective date
| | | ---------------Original Message--------------- From: venki k Sent: Monday, April 09, 2012 11:35 PM Subject: How to Find the Second Highest Max effdt from Job Table in PeopleSoft? How to find the second highest max effdt from job table in PeopleSoft? Please respond! Thanks, venki | | Reply to this email to post your response. __.____._ | _.____.__ |
No comments:
Post a Comment