I am facing some problem in developing an query and looking for some help.
Find all the records where
1)customer_id and product_id should have unique combination.
2)Should have valid date range.(start_date <= today(sysdate) <= end_date)
3)If you have more than one entry for particular customer_id and product_id combination then pick the record with latest modified_date.
Table Name: CUSTOMER_PRICE
Assumptions:Today:26-jul-08
customer_id product_id
price start_date
end_date modified_date
[1] [1] [50][20-jun-2008] [20-aug-2008] [10-jul-2008]
[1] [1] [55][25-jun-2008] [25-jul-2008] [15-jul-2008]
[1] [1] [45][26-jul-2008] [20-sep-2008] [12-jul-2008]
[2] [1] [10][10-AUG-2008] [30-AUG-2008] [11-JUL-2008]
[2] [1] [15][20-JUL-2008] [20-AUG-2008] [10-JUL-2008]
[2] [1] [5][20-JUL-2008] [20-SEP-2008] [08-JUL-2008]
[2] [2] ...
[1] [2] ...
so on
Expected Output customer_id product_id
price start_date
end_date modified_date
[1] [1] [45][26-jul-2008] [20-sep-2008] [12-jul-2008]
[2] [1] [15][20-JUL-2008] [20-AUG-2008] [10-JUL-2008]
My attempts: I have tried different queries but still couldn't get the desired output.Here they are,
I have also tried with distinct keyword.
Technologies used:struts,ejb3,oracle 10gXE
Please ask if you need more information.
Any help?
Thanks
[ July 26, 2008: Message edited by: Vishal Pandya ]