Bug #28389 Nr of workdays calculation like excel
Submitted: 12 May 2007 16:45 Modified: 3 Oct 2009 18:53
Reporter: Ivo van Kamp Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: User-defined functions ( UDF ) Severity:S4 (Feature request)
Version:5.1 OS:Linux (Xubuntu 7.04)
Assigned to: CPU Architecture:Any
Tags: business days, Contribution, DATEDIFF, networkdays, workdays

[12 May 2007 16:45] Ivo van Kamp
Description:
A calc_workdays() function in C which uses the MySQL functions calc_daynr and calc_weekday.

How to repeat:
	/* Calculate nr of day since year 0 in new date-system (from 1615) */

long calc_daynr(int year,int month,int day)
{
  long delsum;
  int temp;
  //DBUG_ENTER("calc_daynr");

  if (year == 0 && month == 0 && day == 0)
    return (0);				/* Skip errors */

  delsum= (long) (365L * year+ 31*(month-1) +day);
  if (month <= 2)
      year--;
  else
    delsum-= (long) (month*4+23)/10;
  temp=(int) ((year/100+1)*3)/4;

  return (delsum+(int) year/4-temp);
} /* calc_daynr */

	/* Calc weekday from daynr */
	/* Returns 0 for monday, 1 for tuesday .... */

int calc_weekday(long daynr,int sunday_first_day_of_week)
{
  //DBUG_ENTER("calc_weekday");
  return ((int) ((daynr + 5L + (sunday_first_day_of_week ? 1L : 0L)) % 7));
}

   /* Calc workdays like excel */
   /* 
      nr of weekends = nr of days / 7
      nr of workdays = nr of days - nr of weekends

      The remainder of the division of nr of days / 7
      could fully or partly comprise a weekend.

      Example:
        12345671234567123456712345671234567123456712345671234567123456712345671234567
                     1234567890123456789 (2 weeks + 5 days)
                     12345678901234 (2 weeks = 14 days )
                     56789          (the remainder of 5 days begins on the same day as the start date)

      The first line in the table below shows that for a remainder of 6 days, when starting
      on the first day of the week, there is one weekendday.

      If startday = 7 then there is only 1 weekendday
      If nr of remaining days - (7 - day of week) + 1 =

         < 1  => 0 weekenddays
         = 1  => 1 weekendday
         >1   => 2 weekenddays (a saturday and a sunday)

      Nr of              Nr of    Calculation attempt
      remaining Startday weekend
      days               days

      6 days    day 1     1        7-1=6  6-6+1 = 1  1
      6 days    day 2     2        7-2=5  6-5+1 = 2
      6 days    day 3     2        7-3=4  6-4+1 = 3
      6 days    day 4     2        7-4=3  6-3+1 = 4
      6 days    day 5     2        7-5=2  6-2+1 = 5
      6 days    day 6     2        7-6=1  6-1+1 = 6
      6 days    day 7     1        7-7=0  6-0+1 = 7  1

      5 days    day 1     0        7-1=6  5-6+1 = 0  0
      5 days    day 2     1        7-2=5  5-5+1 = 1  1
      5 days    day 3     2        7-3=4  5-4+1 = 2
      5 days    day 4     2        7-4=3  5-3+1 = 3
      5 days    day 5     2        7-5=2  5-2+1 = 4
      5 days    day 6     2        7-6=1  5-1+1 = 5
      5 days    day 7     1        7-7=0  5-0+1 = 6  1

      4 days    day 1     0
      4 days    day 2     0
      4 days    day 3     1
      4 days    day 4     2
      4 days    day 5     2
      4 days    day 6     2
      4 days    day 7     1

      3 days    day 1     0
      3 days    day 2     0
      3 days    day 3     0
      3 days    day 4     1
      3 days    day 5     2
      3 days    day 6     2
      3 days    day 7     1

      2 days    day 1     0
      2 days    day 2     0
      2 days    day 3     0
      2 days    day 4     0
      2 days    day 5     1
      2 days    day 6     2
      2 days    day 7     1

      1 days    day 1     0
      1 days    day 2     0
      1 days    day 3     0
      1 days    day 4     0
      1 days    day 5     0
      1 days    day 6     1
      1 days    day 7     1
*/

int calc_workdays(int y1, int m1, int d1, int y2, int m2, int d2)
{
   int days1    = calc_daynr(y1, m1, d1);
   int days2    = calc_daynr(y2, m2, d2);
   int nrofdays = days2 - days1;

   if (nrofdays==0) return 1;  // e.g. monday - monday = 1 day

   int neg_nrofdays = (nrofdays < 0);
   int dayofweek    = calc_weekday((neg_nrofdays ? days2 : days1), 0);

   nrofdays = abs(nrofdays)+1; // e.g. diff mon-tue = 1, but takes 2 days

   int nrofweekends = floor(nrofdays / 7);
   int mod          = nrofdays % 7;
   int add          = 0;

   // Correction when the remainder (part of a week)
   // fully or partly comprises a weekend.
   if (mod)
   {
      if ((dayofweek)==7) add=1;
      else
      {
         add = mod - (7 - dayofweek) + 1;
         if (add<0) add = 0;
         if (add>1) add = 2;
      }
   }

   int workdays = nrofdays - ((nrofweekends*2) + add);

   return (neg_nrofdays ? -workdays : workdays);
}

int main (void)
{
   // Print nr of workdays between May 1th, 2007 and Apr 16th, 2007
   //printf("Nr of workdays: %d\n",   calc_workdays(2007, 5, 1, 2007, 4, 16));

   // Print nr of workdays between May 13th, 2007 and May 19th, 2007
   printf("Nr of workdays: %d\n",   calc_workdays(2007, 5, 13, 2007, 5, 19));
}

Suggested fix:

mysql> SELECT WORKDAYS('2007-5-13','2007-5-19');
        -> 5

mysql> SELECT WORKDAYS('2007-5-19','2007-5-13');
        -> -5
[12 May 2007 17:29] MySQL Verification Team
Thank you for the bug report feature request.
[12 May 2007 22:10] Ivo van Kamp
Bugfix. Misinterpreted day of week..

Attachment: workdays.c (application/octet-stream, text), 4.81 KiB.

[12 May 2007 22:27] Ivo van Kamp
Thank you for having me. If you like I can try to write a patch to create a workdays function.
[19 May 2007 6:38] Ivo van Kamp
Patch file against 5.0.21 to add a workdays function. Apply with patch -Np0 -i [patchfile] in the root dir of the source tree.

Attachment: mysql-5.0.21.workdays.patch (text/x-diff), 3.68 KiB.

[19 May 2007 6:55] Ivo van Kamp
Patch tested on MySQL 5.0.21, Linux Xubuntu 7.04.
[19 May 2007 7:01] Ivo van Kamp
mysql> select workdays('2007-05-13','2007-05-19');
+-------------------------------------+
| workdays('2007-05-13','2007-05-19') |
+-------------------------------------+
|                                   5 | 
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select workdays('2007-05-19','2007-05-13');
+-------------------------------------+
| workdays('2007-05-19','2007-05-13') |
+-------------------------------------+
|                                  -5 | 
+-------------------------------------+
1 row in set (0.00 sec)
[19 May 2007 17:43] Ivo van Kamp
Added tag "Contribution"
[3 Sep 2009 18:53] Liz Drachnik
Hello Ivo

In order for us to continue the process of reviewing your contribution to MySQL - We need
you to review and sign the Sun|MySQL contributor agreement (the "SCA")

The process is explained here: 
http://forge.mysql.com/wiki/Sun_Contributor_Agreement

Getting a signed/approved SCA on file will help us facilitate your contribution-- this
one, and others in the future.

Thank you !
[3 Oct 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".