Help with a Query!!!!

[parul sharma]

Hi!!
I came across a query problem and was wondering if someone could help me out.Here it goes...
suppose there a a table having columns MATERIAL(varchar),COST(number),PDATE(DATE)
and there is data in the table as material name its cost and purchase date example entry(computer,100,12-jul-2002).there r many entries of this form.now the problem is to find out the cost of a material say computer at a particular date(there may be no entry for that desired date so result refect the cost corressponding to the date closest and previous to the desired date for exaple if there r entries for computer on 8th july,10th july and 17th july the results of query for 12th july sholud give the results of 10th july)
Hope someone can help me out as I am at my wits end
Thanx
parul

[SJ Adnams]

do a select on all dates and sort by the difference (and difference < 0) , then just pick the top row (using rownum - you will need at least 8i for this to work, otherwise use distinct to implicity sort)

is that enough to go on?

[Mark Spritzler]

I think the only thing to add would be that the where clause should have something like
date < desired_date
So that way the top one is the date that is closest to the date you want.
I wonder if you can also use the where clause and in the select portion with the date use the MAX function. However, using the MAX funtion you will need to have a GROUP BY "Material and Cost"
Mark

[parul sharma]

Thanx for the help I finally got the answer by first subtract the date from desired date and then take out the tuple having max(date-desired_date) where (date-desired_date)<0.
Thanx once again!!
Parul

[Michael Matola]

This is a really common kind of querying in certain domains. (Sometimes called "effective date" logic, because the date represents a date at which something becomes effective (here, the price) for time forward and which can be superceded by another row on another date.)
Anyhow, the general solution for working with effective dates is to use a "correlated subquery" (not a group by). What makes a correlated subquery different from a noncorrelated subquery (which folks are probably a little more used to seeing) is that the correlated subquery joins an aliased table back to itself, usually (but not always) joined on key fields other than the effective date) to create "groups" based on the fields in the join. Often you take a max or min or somesuch on a value across the group.
It's easier to show the query than explain it. Assuming material and date are key fields on your table, here's the query you want:

SELECT t.material, t.cost, t.date
FROM materials t
where t.date = ( select max( t2.date )
 from materials t2
where t2.material = t.material 
 and t2.date <= [I]your date[/I] )

This is about the simplest possible version of this kind of query. They can get really complicated really fast when you're joining many tables with effective dates, using each others' dates as cutoffs, outer joining them, etc.
When I above said "usually (but not always) joined on key fields other than the effective date" I was being very fussy. I sometimes also describe this as "pay very close attention to your key fields" when doing correlated subqueries. The real story is that in the vast majority of cases, you're going to want to join all the keys (except for effective date), but there are some really fun oddball scenarios where you join on fewer keys.
[ July 22, 2002: Message edited by: Michael Matola ]

[Michael Matola]

And another thing...
If you find yourself commonly doing effective date logic off a table, say, always looking for the current row ( ... where t2.date <= sysdate ... ), it can be helpful to store that query as a view, and just use the view instead of the base table in your queries.