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 !
> 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.
>
>
>