From: Craig \(OFT\) Weston
Date: August 28 2007 5:34pm
Subject: RE: [MYSQL]Time formatting for cycle time.
List-Archive: http://lists.mysql.com/mysql/208766
Message-Id: <E9C66DE22474F54D9152AD9B4191E9BC01BB3A63@EXCNYSM0A1AM.nysemail.nyenet>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Ok, So, What I have come up with (so far) as a variant of Baron's query:



SET @d1 =3D '2007-2-1 00:00:00';# Start date
SET @d2 =3D '2007-2-28 23:59:59';# End date
SET @tdiff =3D TIMEDIFF( TIME(@d1), TIME(@d2) );
SET @dow1 =3D DAYOFWEEK(@d1);
SET @dow2 =3D DAYOFWEEK(@d2);
SET @days =3D DATEDIFF(@d2,@d1);
SET @wknddays =3D 2 * FLOOR( @days / 7 ) +
                IF( @dow1 =3D 1 AND @dow2 > 1, 1,
                    IF( @dow1 =3D 7 AND @dow2 =3D 1, 1,
                        IF( @dow1 > 1 AND @dow1 > @dow2, 2,
                            IF( @dow1 < 7 AND @dow2 =3D 7, 1, 0 )  =20
                          )
                      )
                  );
SET @days2 =3D 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 =3D IF( ASCII(@tdiff) =3D 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.=20

The results:

+------------------------------------+-----------+-------+=20
| CONCAT( @days2, ' days ', @tdiff ) | @wknddays | @days |
+------------------------------------+-----------+-------+
| 17 days 23:59:59                   | 8         | 27    |=20
+------------------------------------+-----------+-------+=20


--------+------------------------+------------------------+----------+
 @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.