Ok then, Now I have a result set so very close to where I want to be. The query thus far:
mysql> SET @d1 = '2007-02-01 18:24:04';# Start date SET @d2 = '2007-2-28 23:05:40';# End date set @wkldays = (select WorkDayDiff`(@d2,@d1)-1); Set @wkldays2 = if(@wkldays < 0,1,0); set @Day_End = (select `business_hours`.`Day_End` from `resource_data`.`business_hours` limit 1); set @Day_Start = (select `business_hours`.`Day_Start` from `resource_data`.`business_hours` limit 1); Set @t1 = (IF((HOUR(@d1))<@Day_Start,7,IF((HOUR(@d1))>@Day_End ,18,HOUR(@d1))))+(IF((HOUR(@d1))<@Day_Start,0,IF((HOUR(@d1))>@Day_End ,0,MINUTE(@d1)))/60)+(IF((HOUR(@d1))<@Day_Start,0,IF((HOUR(@d1))>@Day_En d ,0,SECOND(@d1)))/3600); Set @t2 = (IF((HOUR(@d2))<@Day_Start,7,IF((HOUR(@d2))>@Day_End ,18,HOUR(@d2))))+(IF((HOUR(@d2))<@Day_Start,0,IF((HOUR(@d2))>@Day_End ,0,MINUTE(@d2)))/60)+(IF((HOUR(@d2))<@Day_Start,0,IF((HOUR(@d2))>@Day_En d ,0,SECOND(@d2)))/3600); set @tdif = IF(@t1 > @t2,((22 - @t1) + (@t2 - 11)),@t2 - @t1); select @wkldays, @t1, @t2, @wkldays2, @tdif, ((@[EMAIL PROTECTED])*11)[EMAIL PROTECTED] 'hours'; With the results: +----------+-----+-----+-----------+------- | @wkldays | @t1 | @t2 | @wkldays2 | @tdif +----------+-----+-----+-----------+------- | 17 | 18 | 18 | 0 | 0 +----------+-----+-----+-----------+------- +------------------------------------+ | hours | +------------------------------------+ | 187.000000000000000000000000000000 | +------------------------------------+ My question now, is does anyone know how I could alter this query to get precision in the hours? The idea is to get at least to the minute resolution. I tried ((@[EMAIL PROTECTED])*11)[EMAIL PROTECTED] + 0.0000, but I have few brain cells not on strike. Thanks, 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]
