Ok, So, What I have come up with (so far) as a variant of Baron's query:
SET @d1 = '2007-2-1 00:00:00';# Start date SET @d2 = '2007-2-28 23:59:59';# End date SET @tdiff = TIMEDIFF( TIME(@d1), TIME(@d2) ); SET @dow1 = DAYOFWEEK(@d1); SET @dow2 = DAYOFWEEK(@d2); SET @days = DATEDIFF(@d2,@d1); SET @wknddays = 2 * FLOOR( @days / 7 ) + IF( @dow1 = 1 AND @dow2 > 1, 1, IF( @dow1 = 7 AND @dow2 = 1, 1, IF( @dow1 > 1 AND @dow1 > @dow2, 2, IF( @dow1 < 7 AND @dow2 = 7, 1, 0 ) ) ) ); SET @days2 = FLOOR(@days - @wkndDays) - (IF( @tdiff > 0, 1, 0 ) + (SELECT count(*) FROM `resource_data`.`holidays` WHERE `resource_data`.`holidays`.`date` BETWEEN @d1 AND @d2)); SET @tdiff = IF( ASCII(@tdiff) = 45, SUBSTRING(@tdiff,2), TIMEDIFF( '24:00:00', @tdiff )); SELECT CONCAT( @days2, ' days ', @tdiff ), @wknddays, @days, @days2, @tdiff, IF( @tdiff < 0, 1, 0 ),#Test value 1 IF( @tdiff > 0, 1, 0 ),#Test value 2 FLOOR(@days - @wkndDays), (SELECT count(*) FROM `holidays` WHERE `holidays`.`date` BETWEEN @d1 AND @d2); What I did was reverse the @tdiff equation to add a day for a positive @tdiff instead of subtracting it. My test month, February, has 2 holidays in it. The results: +------------------------------------+-----------+-------+ | CONCAT( @days2, ' days ', @tdiff ) | @wknddays | @days | +------------------------------------+-----------+-------+ | 17 days 23:59:59 | 8 | 27 | +------------------------------------+-----------+-------+ --------+------------------------+------------------------+----------+ @days2 | IF( @tdiff < 0, 1, 0 ) | IF( @tdiff > 0, 1, 0 ) | holidays | --------+------------------------+------------------------+----------+ 17 | 0 | 1 | 2 | --------+------------------------+------------------------+----------+ This seems to account for the remainder better? Of course the original reason it was added was to take into account times that were earlier, which it does not seem to do? Thank you for your help! Craig -------------------------------------------------------- This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]