Urgent

[thakur_at]

Hi all

Can I display Nearest dates before 31-dec-2002 from the table?
my data table shows this..(table name test)
P_REF P_ID P_DT P_RSN
---------- ---------- --------- -------
D052 1916 24-JUL-00 new
d052 2779 04-JAN-01 act
d052 5156 01-OCT-02 act
d053 1920 24-JUL-00 new
d053 3134 09-MAY-01 act
d053 4587 11-OCT-02 act

here nearest dates to 31-dec-02 are 01-oct-02 and 11-oct-02
How to get these dates with the help of sql query?

awaiting reply...

thanks in advance
Amit

[jovery]

Try something like:

SELECT .... FROM tablename
WHERE datecolumn >= sysdate-(number_of_days_proximity)

eg.

SELECT values FROM table
WHERE datecolumn >= sysdate - 21

Will show all colums whose date is within the last 21 days

Hope that helps

[raguram]

Hi Amit,

I hope the query will solve your prob..

create table test (P_REF varchar2(10), P_ID number , P_DT date , P_RSN varchar2(10))

insert into test values('D052', 1916,'24-JUL-00', 'new');
insert into test values('d052', 2779,'04-JAN-01', 'act');
insert into test values('d052', 5156,'01-OCT-02', 'act');
insert into test values('d053', 1920,'24-JUL-00', 'new');
insert into test values('d053', 3134,'09-MAY-01', 'act');
insert into test values('d053', 4587,'11-OCT-02', 'act');

select b.PR,b.PID,b.PDT,b.RSN from (
select a.P_REF PR,a.P_ID PID ,a.P_DT PDT ,a.P_RSN RSN ,rownum rno from
(select P_REF, P_ID,P_DT, P_RSN from test where P_DT <='31-DEC-02' order by P_DT desc)
a)
b WHERE rno <= 2;

decide rno based on how many Nearest values you need.

If got any other way let me know it.

Regards,
Ragu

[thakur_at]

Thank a lot
But problem is year can be universal..so, we cannot assume the proximity..means insead of dec-02 query can be for dec-96 or jul-01 or something similar..
Thanks
awaiting reply..
Ami

[ales]

If you don't know how many days the nearest date is:
- if you need the nearest date:

select max(P_DT) from the_table
 where P_DT<to_date('31-dec-2002','dd-mon-yyyy');

- if you need more dates:

select P_DT from
(
  select P_DT from the_table
   where P_DT<to_date('31-dec-2002','dd-mon-yyyy')
   order by P_DT desc
)
where rownum<=number_of_the_nearest_dates_you_want_to_get;

[pando]

dupe

http://forums.dbasupport.com/forums/...threadid=32268