working days calculation query - working but not able to understand

Question

I wanted to calculate number of working days for an year (excluding only saturdays and sundays) and got this query from web and its working..but i am not able to understand this query from MID part..can anyone help me to understand this...

SELECT 5 * (DATEDIFF('2015-12-31', '2015-01-01') DIV 7) +
    MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY('2015-12-31') +
    WEEKDAY('2015-01-01') + 1, 1)

i am not able to understand from

MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY('2015-12-31') +
WEEKDAY('2015-01-01') + 1, 1)

@Tim: Its with the explaination from here: http://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates/6762805#6762805 — SophieXLove64, Jul 22 '15 at 07:07

score 3 · Accepted Answer · answered Jul 22 '15 at 07:08

WEEKDAY('2015-12-31') and WEEKDAY('2015-01-01') are computing the day of the week for the start and end of the year, with 0 = Monday and 6 = Sunday. For the year 2015, these are both 6 (Sunday). So the middle argument of MID is 7 * 6 + 6 + 1, or 49.

The 49th character of the long string is 0. So this means it will add zero to the rest of the expression.

The purpose of this expression is to adjust the weekday count according to what days of the week the first and last day of the year are. In 2015, neither one is a weekday, so it adds zero. Now consider 2016. Jan 1 2016 is a Monday (weekday=1) and Dec 31 2016 is a Tuesday (weekday=2). 7*1+2+1 equals 10. The tenth character of the long string is 1. So it will add one weekday ... intuitively we can see this happens because 2016 is a leap year, so there's one more day in the year. And so on. For each possible beginning and ending day of the year, the long string encodes an adjustment of how many weekdays to add. I imagine that the string was discovered by trial and error.

And yes, it did really hurt to stare at this query for the last 10 minutes. — Tim Biegeleisen, Jul 22 '15 at 07:09
Thanks to @Bioshade's link above, I see that instead of trial and error, the long string has some logic to it. Oh well. — Ross Presser, Jul 22 '15 at 07:09

working days calculation query - working but not able to understand

1 Answers1