This works if you don't care about holidays. If you do the only solution that I have seen that works is to create a business day table. Ours is shown below. You have to hand construct the calendar by removing weekends and holidays for the specific entity. This calendar forces a non business day DATE to the next business day. Date arithmetic then becomes simple including FIRST MONDAY OF THE MONTH in the US where many holidays fall on Monday.
clnd_Day is the relative business day since 2000-01-01 clnd_Week is the relative business week since 2000-01-01 mysql> show create table calendar; +----------+------------------------------------------------- ------------------------------------------------------------- | Table | Create Table +----------+------------------------------------------------- ------------------------------------------------------------- | calendar | CREATE TABLE `calendar` ( `clnd_Day` smallint(5) unsigned NOT NULL default '0', `clnd_Date` date NOT NULL default '0000-00-00', `clnd_Week_Day_Txt` char(9) default NULL, `clnd_Week_Day_Num` tinyint(3) unsigned default NULL, `clnd_Char_Date` char(12) default NULL, `clnd_Week` smallint(8) unsigned default NULL, `clnd_Real_Date` char(10) default NULL, PRIMARY KEY (`clnd_Date`), UNIQUE KEY `clnd_Real_Date_IDX` (`clnd_Real_Date`), KEY `clnd_Day_IDX` (`clnd_Day`), KEY `clnd_Char_Date` (`clnd_Char_Date`) ) TYPE=InnoDB | mysql> select * from calendar limit 10; +----------+------------+-------------------+-------------------+----------- -----+-----------+----------------+ | clnd_Day | clnd_Date | clnd_Week_Day_Txt | clnd_Week_Day_Num | clnd_Char_Date | clnd_Week | clnd_Real_Date | +----------+------------+-------------------+-------------------+----------- -----+-----------+----------------+ | 1 | 2000-01-01 | Monday | 2 | 01/03/2000 | 1 | 01/01/2000 | | 1 | 2000-01-02 | Monday | 2 | 01/03/2000 | 1 | 01/02/2000 | | 1 | 2000-01-03 | Monday | 2 | 01/03/2000 | 1 | 01/03/2000 | | 2 | 2000-01-04 | Tuesday | 3 | 01/04/2000 | 1 | 01/04/2000 | | 3 | 2000-01-05 | Wednesday | 4 | 01/05/2000 | 1 | 01/05/2000 | | 4 | 2000-01-06 | Thursday | 5 | 01/06/2000 | 1 | 01/06/2000 | | 5 | 2000-01-07 | Friday | 6 | 01/07/2000 | 1 | 01/07/2000 | | 6 | 2000-01-08 | Monday | 2 | 01/10/2000 | 2 | 01/08/2000 | | 6 | 2000-01-09 | Monday | 2 | 01/10/2000 | 2 | 01/09/2000 | | 6 | 2000-01-10 | Monday | 2 | 01/10/2000 | 2 | 01/10/2000 | +----------+------------+-------------------+-------------------+----------- -----+-----------+----------------+ 10 rows in set (0.00 sec) -----Original Message----- From: Mike Rains [mailto:[EMAIL PROTECTED] Sent: Monday, February 21, 2005 9:33 AM To: mysql@lists.mysql.com Subject: Re: how to write this query? SELECT start_date, end_date, DATEDIFF(end_date, start_date) - (WEEK(end_date) - WEEK(start_date)) * 2 AS business_days FROM DateDiffs ORDER BY start_date; +---------------------+---------------------+---------------+ | start_date | end_date | business_days | +---------------------+---------------------+---------------+ | 2005-01-04 16:44:57 | 2005-01-10 17:53:33 | 4 | | 2005-01-17 00:00:00 | 2005-02-18 00:00:00 | 24 | | 2005-01-21 00:00:00 | 2005-02-18 00:00:00 | 20 | | 2005-01-24 00:00:00 | 2005-02-18 00:00:00 | 19 | | 2005-01-28 00:00:00 | 2005-02-18 00:00:00 | 15 | | 2005-01-31 00:00:00 | 2005-02-18 00:00:00 | 14 | | 2005-02-04 00:00:00 | 2005-02-18 00:00:00 | 10 | | 2005-02-07 00:00:00 | 2005-02-18 00:00:00 | 9 | | 2005-02-14 00:00:00 | 2005-02-18 00:00:00 | 4 | +---------------------+---------------------+---------------+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
