Need help in an query development

[Vikas Kapoor]

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,

select customer_id,product_id from customer_price 
where start_date <= sysdate and end_date >= sysdate
group by customer_id,product_id;

select customer_id,product_id,price,modified_date from customer_price
where start_date <= sysdate and end_date >= sysdate
order by modified_date desc;

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 ]

[Agador Paloi]

I think you are close, the group by and use MAX(modified_date) to satisfy your third requirement .

[Vikas Kapoor]

Hello Agador,

Thanks for your reply(Atleast/last i got one).

I figured out the problem (using only queries)like this ,

select customer_id, product_id, max(modified_date) from customer_price    
 where start_date <= sysdate and end_date >= sysdate group by customer_id, product_id;

And the output of first query is fed to next query.

select price from pri_customer_price where customer_id=  
 :customerId and product_id= :productId and start_date <= sysdate and 
 end_date >= sysdate and modified_date = :modifiedDate;

Can this be done in better way?
[ July 29, 2008: Message edited by: Vishal Pandya ]

[Hemavathy Ramamoorthy Gopal]

Hi,

Please try this and let me know whether it met your requirement or not since I dont have oracle set up.

select cust_id,prod_id from cust_table
where m_dt in (select max(m_dt) from cust_table where st_dt <= sysdate and en_dt >= sysdate group by cust_id,prod_id)

Note : m_dt should contain unique data.

[Vikas Kapoor]

Originally posted by Hemavathy Ramamoorthy Gopal:
Note : m_dt should contain unique data.

Practically,many records can be modified on the same day.

[Hemavathy Ramamoorthy Gopal]

Hi,

As you said in practical it will get change for that purpose only we are using group by clause to get a unique record in order to make more perfect group by clause can be used in main query also.

[Hemavathy Ramamoorthy Gopal]

Hi Vishal,

I am egar to know whether the given query has meet your requirement or not because I framed out this query out visually since I don't have any setup to crosscheck the query. Please give me descriptive explanation in what ways it is not meeting your requirement.

[Vikas Kapoor]

The assumption that you have made is wrong. More than one record could be updated on the same day so we can't have unique data for modified_date.i.e.

Note : m_dt should contain unique data.

This isn't possible.

[Hemavathy Ramamoorthy Gopal]

Hi Vishal,

Check this out.

select distinct cust_id,prod_id, max(md_dt) from cust_price
where md_dt in (select max(md_dt) from cust_price where st_dt <= sysdate and en_dt >= sysdate group by cust_id,prod_id)
group by cust_id,prod_id;