|
Hi all,
I have a data as given below.
ID RATE1 DATE1 RATE2 DATE2 CODE
10 A 2007-FEB-01 U 2007-JAN-01 BIG
10 B 2007-FEB-01 V 2007-JAN-01 BIG
10 C 2007-JAN-01 W 2007-FEB-01 SMALL
10 D 2007-FEB-01 X 2007-JAN-01 SMALL
I have to write a query for which I will describe my requirement step by step as given below
We will first aim to fetch rows based on ID,RATE1,DATE1
STEP1:
I need to fetch rows based on ID,RATE1,DATE1 with the rows having latest DATE1 for same ID(here 100).So my output will be as follows
ID RATE1 DATE1 CODE
10 A 2007-FEB-01 BIG
10 B 2007-FEB-01 BIG
10 D 2007-FEB-01 SMALL
STEP2:-
Now from the output of as got in step1,we need to fetch rows based on CODE (i.e) if the CODE is BIG and SMALL for selected rows then we need select only rows having BIG only else select rows having CODE=SMALL.So my output will be as follows
ID RATE1 DATE1 CODE
10 A 2007-FEB-01 BIG
10 B 2007-FEB-01 BIG
So output from step2 will be for ID,RATE1,DATE1.
Now we need to fetch rows based on ID,RATE2,DATE2
STEP3:
I need to fetch rows based on ID,RATE2,DATE2 with the rows having latest DATE2 for same ID(here 100).So my output will be as follows
ID RATE1 DATE1 CODE
10 W 2007-FEB-01 SMALL
STEP4:-
Now from the output of as got in step1,we need to fetch rows based on CODE (i.e) if the CODE is BIG and SMALL for selected rows then we need select only rows having BIG only else select rows having CODE=SMALL.So my output will be as follows
ID RATE1 DATE1 CODE
10 W 2007-FEB-01 SMALL
So output from step4 will be for ID,RATE2,DATE2.
On combining the output from step2 and step4 I need the final output as given below
ID RATE1 DATE1 RATE2 DATE2 CODE
10 A 2007-FEB-01 W 2007-FEB-01 BIG
10 B 2007-FEB-01 W 2007-FEB-01 BIG
10 B 2007-FEB-01 W 2007-FEB-01 SMALL
Could anyone please provide an oracle query for the given requirement.
Thanks in advance,
Regards,
Laxmi
| 
