List:General Discussion« Previous MessageNext Message »
From:Craig \(OFT\) Weston Date:August 28 2007 5:34pm
Subject:RE: [MYSQL]Time formatting for cycle time.
View as plain text  
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.

Thread
[MYSQL]Time formatting for cycle time.Craig \(OFT\) Weston27 Aug
  • Can't start mysql Feliks Shvartsburd28 Aug
  • Re: [MYSQL]Time formatting for cycle time.Peter Brawley28 Aug
    • RE: [MYSQL]Time formatting for cycle time.OFT)28 Aug
      • Re: [MYSQL]Time formatting for cycle time.Baron Schwartz28 Aug
        • RE: [MYSQL]Time formatting for cycle time.Craig \(OFT\) Weston28 Aug
          • Re: [MYSQL]Time formatting for cycle time.Peter Brawley28 Aug
      • RE: [MYSQL]Time formatting for cycle time.Craig \(OFT\) Weston28 Aug
RE: [MYSQL]Time formatting for cycle time.Craig \(OFT\) Weston29 Aug