Peek max record on selection of date range

[rupaliqlik]

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

sunny t  kaushik.solanki Sunny_talwar

[Lauri]

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.