Re: Can I use the concept of Effective Date with MySQL?
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
RE: Can I use the concept of Effective Date with MySQL?
SELECT price FROM mytable WHERE price = theprice DESC LIMIT 1; -Original Message- From: Alejandro Zuzenberg [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 01, 2001 7:53 PM To: [EMAIL PROTECTED] Subject: Can I use the concept of Effective Date with MySQL? 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. Thanks! Alejandro [EMAIL PROTECTED] _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - 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 - 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
RE: Can I use the concept of Effective Date with MySQL?
I have had to do this problem and i solved it by having 4 fields, Normalprice (PrPrice), Tempprice (PrTempPrice), Start date (PrStart) End Date (PrStop). In the select IF(((CURDATE() = PRStart) and (PRStop = CURDATE())), PrTempPrice, PrPrice) as CurrentPrice hope this helps On Thu, 1 Nov 2001, Rick Emery wrote: Date: Thu, 1 Nov 2001 17:20:00 -0600 From: Rick Emery [EMAIL PROTECTED] To: 'Alejandro Zuzenberg' [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: Can I use the concept of Effective Date with MySQL? SELECT price FROM mytable WHERE price = theprice DESC LIMIT 1; -Original Message- From: Alejandro Zuzenberg [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 01, 2001 7:53 PM To: [EMAIL PROTECTED] Subject: Can I use the concept of Effective Date with MySQL? 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. Thanks! Alejandro [EMAIL PROTECTED] _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - 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 - 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 Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - 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
