• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Need help in an query development

 
Vikas Kapoor
Ranch Hand
Posts: 1374
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Agador Paloi
Ranch Hand
Posts: 118
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you are close, the group by and use MAX(modified_date) to satisfy your third requirement .
 
Vikas Kapoor
Ranch Hand
Posts: 1374
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Agador,

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

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

And the output of first query is fed to next query.
Can this be done in better way?
[ July 29, 2008: Message edited by: Vishal Pandya ]
 
Hemavathy Ramamoorthy Gopal
Greenhorn
Posts: 17
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1374
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 17
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 17
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1374
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 17
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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;
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic