I've got the same issue that you do. Here is my thinking thus far: Say I have a transaction table: Record_Key Service_Date Item_Code Item_Quantity
I'm considering this structure for the rate table: Item_Code Effective_Date Rate It's practical from a data entry perspective, but I can't seem to write a single query that says 'join the transaction table to the rate table where Service_Date >= Effective_Date, and only get the latest rate record for each item_code'. The assumption here is that there is only a record added to the table when a new rate is about to be implemented. Of course, one could use client side logic and multiple queries to get the desired result. It's more likely I'll wind up with this kind of rate table: Item_Code Effective_Date_From Effective_Date_Thru Rate At least then, I can join by item code and use a where clause to restrict the transaction service date to be within the effective date range. The only issue here is that I'll need quite good control over the data entry process to be sure that date ranges never overlap, or the join mechanism could return more than one rate record, and perhaps cause an incorrect result. I have also considered a somewhat novel alternative, which is to use the first table structure, but use it to build a table that has each and every day populated for each item. That way, an exact join could always be made by Item Code and Service Date. The drawback here is that it wastes a lot of space just to make a convenient join. 1000 items for one year would require 365,000 records, a considerable waste of space. If anyone has a more reasonable idea, please add to the discussion. Regards, Rich At 07:52 PM 11/1/01, Alejandro Zuzenberg wrote: >I need to select the appropriate price for a product in a table, and the >product has a compund key with 2 fields: product number and date. >For every transaction with a certain product, I need to retrieve the price >that was 'current' at the specific time when that transaction took place. > >In joining two tables, I need to relate the price of a product to every >sales transaction where that product was used, not only by product number >but also by the effective date for that price. > >How can I do this? Is there a 'closest' function (the highest value that is >< or = ) to be used in a WHERE statment ? >I've been searching for this for hours and could not fnd a solution with >MySql. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
