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
