Count days excluding weekends

vazquezjm · #1 (**permalink**) 02-02-04, 10:48

Hi.

I need to get the number of days between 'date' field from a table and current day (now()).

Currently I'm using this:

SELECT (TO_DAYS(NOW()) - TO_DAYS(date)) AS days

but it counts weekend days also and I only want to count business days (Mon to Fri). Is there any way to do this?

Thanks.

r937 · #2 (**permalink**) 02-02-04, 12:44

if you really and truly want to count business days. then you will need a calendar table to store holidays

if you just want monday through friday regardless of whether it's a holiday, then yes, there is a way, but it is exceedingly complicated

use DAYOFWEEK(date) or WEEKDAY(date) to get the day of the week of both the original date and today

do the subtraction as you currently have it

adjust the answer by a certain number based on
- the number of full weeks in the answer
(divide by seven, multiply by two)
- whether the original date's day number is greater than today's day number

for example, february 29 is day 60, today is day 33, difference is 27, you have to throw away 27/7 = 4, times 2 is 8 days, and the adjustment is 0 days because today is weekday 2 and feb 29 is weekday 1, so 27 - 8 = 19

okay, i admit, i cheated, i looked at my wall calendar

like i said, very complicated

with a calendar table, however...

Code:

caldate    wkday   holiday
2004-02-01   1   
2004-02-02   2   
2004-02-03   3   
2004-02-04   4   
2004-02-05   5   
2004-02-06   6   
2004-02-07   7   
2004-02-08   1   
2004-02-09   2   
2004-02-10   3   
2004-02-11   4   
2004-02-12   5   
2004-02-13   6   
2004-02-14   7   
2004-02-15   1   
2004-02-16   2   President's Day
2004-02-17   3
2004-02-18   4
2004-02-19   5
2004-02-20   6
2004-02-21   7
2004-02-22   1
2004-02-23   2
2004-02-24   3
2004-02-25   4
2004-02-26   5
2004-02-27   6
2004-02-28   7
2004-02-29   1
2004-03-01   2
2004-03-02   3
2004-03-03   4
2004-03-04   5
2004-03-05   6
2004-03-06   7

the query is really simple:

Code:

select count(*)
  from calendar
 where caldate between current_date and 'yourdate'
   and weekday in (2,3,4,5,6)
   and holiday is null

aus · #3 (**permalink**) 02-03-04, 01:39

Like Rudy said, it is a complicated query. Doing the calculations is not too bad, messy, but not too bad. You can break it down into a few cases and solve it with IF statements, or you can use a few math tricks and solve it that way. There are a few key elements to the calculation. First, you need to know the day of the week of day 1. Second, you need to know how many days of an incomplete week you have. Third, how to determine if that incomplete week covers a Saturday or a Sunday. The query looks something like this:

Code:

SELECT @ttldays:=DATEDIFF(day2,day1) AS ttldays, @fullwkends:=2*FLOOR(@ttldays/7) AS fullweekends, @lftovr:=MOD(@ttldays,7) AS LeftOvers, 
@others:=GREATEST(LEAST(WEEKDAY(day1)+@lftovr-6,2),0) As LeftOverWeekend, @ttldays - @fullwkends - @others AS AllTheWeekDays
FROM testdays;

I tested this with the table testdays (producing the result file attached):

Code:

CREATE TABLE `testdays` (
  `day1` date default NULL,
  `day2` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The algorithm may not be exactly right in all cases. I tested a few out that were right to me. It may need some tweaking. The thought behind the assignment to the @others piece is that the day of the week (numbered starting with 0 for Monday) of day 1 plus the number of days in the incomplete week minus 6 will give a result in the range of -6 to 6. If the result is less than 0, it doesn't cover the weekend (for instance Monday plus two days is Wednesday: 0 + 2 - 6 = -4). If it is greater than two, then it covers a complete weekend (Friday plus 4 days is Tuesday: 4 + 4 - 6 = 2). If the number is less than 0, we don't want to add days, so just replace those numbers with 0. If the number is greater than 2, just replace it with 2, because we don't want to subtract more days.

Please play with the algorithm and see if there is anything wrong with it.

r937 · #4 (**permalink**) 02-03-04, 06:21

wow

thanks, aus, for running with that

but does your algorithm handle holidays?

heh heh

vazquezjm · #5 (**permalink**) 02-03-04, 06:57

Thank you both!

aus · #6 (**permalink**) 02-03-04, 16:13

Quote:

Originally posted by r937
wow

thanks, aus, for running with that

but does your algorithm handle holidays?

heh heh

The trick would be combining both of our solutions!

r937 · #7 (**permalink**) 02-03-04, 16:57

no offence, but if there's a calendar table as part of the solution, then that's all that's required

select count(*)
from calendar
where caldate between current_date and 'yourdate'
and weekday in (2,3,4,5,6)
and holiday is null

aus · #8 (**permalink**) 02-03-04, 17:04

I was thinking that there could be a weekday-holiday table that could be incorporated into the solution. This way, only the holidays that should not be counted are stored, reducing the storage requirements for the table. The query would be like this:

Code:

SELECT @holidays:=count(*) FROM weekdayholidays WHERE holidaydate BETWEEN day1 AND day2;
SELECT @ttldays:=DATEDIFF(day2,day1) AS ttldays, @fullwkends:=2*FLOOR(@ttldays/7) AS fullweekends, @lftovr:=MOD(@ttldays,7) AS LeftOvers, 
@others:=GREATEST(LEAST(WEEKDAY(day1)+@lftovr-6,2),0) As LeftOverWeekend, @ttldays - @fullwkends - @others - @holidays AS AllTheWeekDays
FROM testdays;

This way, we can both get credit for a solution

Rdean · #9 (**permalink**) 10-27-04, 10:24

Those are really interesting answers to what is actually a deceptively tricky problem!

I am trying to now extend this to find all future information given only a start date and the number of business days to look forward (as opposed to counting days between 2 given dates). The most simplistic example would be given today's date and finding out the date in 10 'business days' time. Has anyone had a crack at this? I like the samples shown above as having the calendar table allows you to define other non-standard non-business days (i.e. work shutdowns etc). However, most other examples of this tend to use some kind of code (VB etc) to hack about with the data rather than using one query which I would like to do.

Richard

Internet Services

Web Development

Digital Marketing

Consumer Tech