Mike Yearwood
Toronto, Ontario, Canada
General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Hi all.
I have 2 tables, one is basically a part list, the other is a price history. The price history has an effective date. All tables and variable names have been changed to protect the identities of the innocent <g>.
Some parts may not have any pricing, so I used an outer join.
The query I want should give the "current" price ... as of the current date/time.
This works when the newer record has a higher record number in the table, but not when I entered the data in reverse. What do I have to do to make it always dependant on the effective date, not on the record sequence? I don't plan to allow the client to edit old records, but I might try to recycle deleted records and this will be a problem.
select ;
parts.part_pk, ;
max(nvl(price_history.effective,dtot({}))) as Effective_Date, ;
nvl(price_history.Price,0000000.00) as Current_Price ;
from parts ;
left outer join price_history on ;
parts.part_pk = price_history.part_fk ;
group by ;
parts.part_pk
Thanks!
Mike
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only