From: Peter Brawley
Date: August 28 2007 8:16pm
Subject: Re: [MYSQL]Time formatting for cycle time.
List-Archive: http://lists.mysql.com/mysql/208769
Message-Id: <46D48296.1040901@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Craig,

Right you are, here is a corrected func:

DROP FUNCTION IF EXISTS BizDateTimeDiff;
DELIMITER |
CREATE FUNCTION BizDateTimeDiff( d1 DATETIME, d2 DATETIME )
RETURNS CHAR(30)
DETERMINISTIC
BEGIN
  DECLARE dow1, dow2, days, wknddays INT;
  DECLARE tdiff CHAR(10);
  SET dow1 = DAYOFWEEK(d1);
  SET dow2 = DAYOFWEEK(d2);
  SET tdiff = TIMEDIFF( TIME(d2), TIME(d1) );
  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 @tdiff = tdiff;
  SET days = FLOOR(days - wkndDays) - IF( ASCII(tdiff) = 45, 1, 0 );
  SET tdiff = IF( ASCII(tdiff) = 45, TIMEDIFF( '24:00:00', 
SUBSTRING(tdiff,2)), TIMEDIFF( tdiff, '00:00:00' ));
  RETURN CONCAT( days, ' days ', tdiff );
END;
|
DELIMITER ;
SELECT BizDateTimeDiff( '2007-1-1 00:00:00', '2007-3-31 00:00:00' ) AS 
dtdiff;
SELECT BizDateTimeDiff( '2007-1-1 11:00:00', '2007-3-31 00:00:00' ) AS 
dtdiff;
SELECT BizDateTimeDiff( '2007-1-1 12:00:00', '2007-3-31 13:00:00' ) AS 
dtdiff;
SELECT BizDateTimeDiff( '2007-1-1 00:00:00', '2007-3-31 11:00:00' ) AS 
dtdiff;

PB

-----

Weston, Craig (OFT) wrote:
> 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.
>
>
>