From: Weston, Craig \(OFT\) (Craig.Westonoft.state.ny.us)
Date: Tue Aug 28 2007 - 12:34:23 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]