Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    2

    Unanswered: Count days excluding weekends

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: Count days excluding weekends

    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.
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wow

    thanks, aus, for running with that

    but does your algorithm handle holidays?

    heh heh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    2
    Thank you both!

  6. #6
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    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!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    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

  9. #9
    Join Date
    Nov 2003
    Posts
    17
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •