Sunday, 15 June 2014

sql - MySQL function to find the number of working days between two dates -


Excel has a NETWORKDAYS () function that finds the number of business days between two dates.

What is the same function for MySQL? Because holidays combine complexity, so there is no solution to deal with holidays.

This expression - 

  5 * (DATEDIFF (@E, @ S) 7) + MID ('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY (@S) + WEEKDAY (@E) + 1, 1)

Start date @ S and end date @ E Calculates the number of business days between

is not before the date of commencement of the expiration date (@E) (@s). It is compatible with DATEDIFF that the same start date and expiry date gives zero working days. Ignore holidays.

The string of digits is constructed as follows. Create a table of early days and expiration days, the lines will start on Monday (WEEKDAY 0) and the columns must start with Monday. Fill the left-to-right right-hand side with all 0 (i.e., Monday and Monday, Tuesday and Tuesday, etc.). To start with a diagonal every day (always be 0) and one day at a time, fill the column on the right. If you land on the weekend (non-business day) column, the number of business days does not change, it is done from left. Otherwise, the number of business days increases by one when you reach the beginning of the line at the end of the line end and continue until you reach the diagonal again. Then go to the next line.

For example, do not say business days on Saturdays and Sundays - 

  | MT TWT FSS - | -------------- M | 0 1 2 3 4 4 4 T4 0 1 2 3 3 3 W | 3 4 0 1 2 2 2 T | 2 3 4 0 1 1F | 1 2 3 4 0 0 S 1 2 3 4 5 0 S 1 2 3 4 5 5 0

Then insert 49 values ​​into the string in the table.

Please tell me if you will get a bug.

-Executed Correction table: 

  | MT TWT FSS - | -------------- M | 0 1 2 3 4 4 4 T4 0 1 2 3 3 3 W | 3 4 0 1 2 2 2 T | 2 3 4 0 1 1F | 1 2 3 4 0 0 S 0 1 2 3 4 0 S Improved string: '0123444401233334012222340111123400001234000123440'  
 Expression of improvement:  
  5 * (DATEDIFF (@E, @S) DIV 7) + Mid ('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY (@s) + WEEKDAY (@E) + 1, 1)

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)