0
[start_date] + interval ([day_increment] div 6) * 7 + 
MID('012345012346012356012456013456023456012345', 6 * WEEKDAY([start_date]) + 
mod([day_increment],6) + 1, 1) + if(mod([day_increment], 6) = 0, -1, 0) day

Stumbled upon the above which calculates your end_date taking into account non working days (Sunday). This is for a 6 day work week, where Sunday is a non-working day. I know what each part does, however I'm having trouble understanding WHY it was done.

Specific parts I am confused about are:

  • [day_increment] div 6) * 7 Is my understanding correct that this calculates how many weeks are in [day_increment], converted to days via multiplying 7? E.g. increment by 16 days, there are 2 weeks worth of business days (16 div 6),* 7, there are 14 days baseline. I'm not exactly sure why 6 and 7 were chosen.
  • '012345012346012356012456013456023456012345' how was the matrix of this concatenated string generated? Logic doesn't seem to follow that explained here: link
  • 6 * WEEKDAY([start_date]) + mod([day_increment],6) + 1, 1) why are we multiplying by 6? What is 6? My guess is that we convert this to a multiple of 6 because there are 6 working days? Furthermore, why are we taking the modulo of increment divided by 6? What is the modulo for, and is 6 the number of business days?

Bulletpoint 2 Update:

I think I've figured out how the string was generated. It's a matrix made up of days of week on the row labels, then n + x as column labels, where x is the number of business days. Values are the calendar days to increment by.

Image of matrix

neldeles
  • 588
  • 1
  • 5
  • 12
  • 6, 7: 6 days in a work week, 7 days in a calendar week... and the "array" is missing the first 6, the second 5, the third 4, the fourth 3, fifth 2, and 6th 1... so it appears to be an array of 7 sets of 6 values, mapping offsets in a range of 1 to 42 (6 * 7), which repeats after 42 days (hence increment div 6, times 7 to find the array index to use)... maybe? – Michael - sqlbot Jul 14 '18 at 23:34
  • Thanks for the input. While it didn't give a clearcut answer, it did get me to approach it from a different POV. Was able to decipher how the array was generated. – neldeles Jul 15 '18 at 10:12

0 Answers0