Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a JOB_RPT table where I applied logic for latest record,now challenge is that I received one sql query where user filter record in front end by selecting date range.So that my record should reduced according to date range.But logic failed to achieve.Below max record is 3/1/2001 but if he select date <=1/1/2000 then latest record should be 1/1/2000.Please help me.
(SELECT MAX(A_ED.EFFDT) FROM sysadm.PS_JOB_RPT A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A_ED.EFFDT <= TO_DATE(:1,'YYYY-MM-DD')) ------ it is variable in peoplesoft tool wherre user pass value
661 | 8/1/1994 | District Manager |
661 | 8/1/1995 | District Manager |
661 | 1/1/1996 | Mgr |
661 | 1/3/1997 | Mgr |
661 | 8/11/1997 | Dir |
661 | 1/1/1998 | Dir |
661 | 8/1/1998 | Dir |
661 | 3/1/1999 | Dir |
661 | 5/1/1999 | Dir |
661 | 11/17/1999 | Dir |
661 | 1/1/2000 | VP |
661 | 2/1/2001 | VP |
661 | 3/1/2001 | VP |
I think Peek is the right function to use. Try this:
tblUserDate:
Load date(MDate) as UserMaxDate;
SQL SELECT MAX(A_ED.EFFDT) as MDate FROM sysadm.PS_JOB_RPT A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A_ED.EFFDT <= TO_DATE(:1,'YYYY-MM-DD');
Let vUserDate = Peek('UserMaxDate', 0, 'tblUserDate');
You can drop tblUserDate.
Now use vUserDate to filter your table.