Reply from plucas on Apr 10 at 11:47 AM select a.* from ps_job a
where a.effdt =
( select max(a1.effdt)
from ps_job a1
where a1.emplid = a.emplid
and a1.empl_rcd = a.empl_rcd
and a1.effdt <
( /** The effdt from the sub query below **/
/** This returns the highest effdt row from ps_job for one emplid **/
/** There may be multiple sequesce numbers but this does not matter. **/
select c.eddft from ps_job c
where c.effdt = (select max(c1.effdt)
from ps_job c1
where c1.emplid = c.emplid
and c1.empl_rcd = c.empl_rcd
and c1.ffdt <= sysdate)
)
and a.effseq = (select max(a2.effseq)
from ps_job a2
where a2.emplid = a.emplid
and a2.empl_rcd = a.empl_rcd
and a2.effdt = a.effdt)
| | | ---------------Original Message---------------
From: jayasathya_venkataraman
Sent: Tuesday, April 10, 2012 11:32 AM
Subject: How to Find the Second Highest Max effdt from Job Table in PeopleSoft?
Hi venki,
You got to add up max(effseq) to this following sql. this is my logic...see
if it is working out for
you.
select a.effdt,a.emplid from ps_job a where a.effdt=(select max(c.effdt)
from ps_job c where c.effdt < (select max(b.effdt) from ps_job b where
b.effdt<sysdt))
Also let me know if there is something wrong with my logic
sathya |
| Reply to this email to post your response. __.____._ |   _.____.__ |
No comments:
Post a Comment