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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
