用于查找两个日期之间的工作天数的MySQL函数

Question

Excel具有NETWORKDAYS()函数，用于查找两个日期之间的营业天数。

有没有人对MySQL有类似的功能？由于假日增加了复杂性，因此解决方案不必处理假日。

Answer 1

这个表达式-

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

计算开始日期@S和结束日期@E之间的营业天数。

假定结束日期(@E)不在开始日期(@S)之前。与DATEDIFF兼容，因为相同的开始日期和结束日期表示零个工作日。忽略节假日。

该数字字符串的构造如下。创建开始天数和结束天数的表，行必须以星期一(工作日0)开头，列也必须以星期一开始。从左上角到右下角用全0填写对角线(即周一到周一、周二到周二等0个工作日)。对于每一天，从对角线开始(必须始终为0)，并填写右侧的列，一天一次。如果您位于周末(非工作日)列，则营业天数不会发生变化，而是从左侧开始。否则，营业天数将增加1。当您到达行尾时，循环回到同一行的开始处，然后继续，直到再次到达对角线。然后转到下一行。

例如，假设周六和周日不是工作日-

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 1 2 3 4 5 0 0
S| 1 2 3 4 5 5 0

然后将表中的49个值连接到字符串中。

如果你发现了任何bug，请告诉我。

-Edit改进后的表格：

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 4 0

改进后的字符串：'0123444401233334012222340111123400001234000123440‘

改进的表达式：

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

Answer 2

除了生成矩阵的方法要复杂得多之外，该解决方案使用的方法与Rodger的方法基本相同。注意:此解决方案的输出与NETWORKDAYS不兼容。

与Rodger的解决方案一样，它计算开始日期(@S)和结束日期(@E)之间的营业天数，而不必定义存储过程。它假定结束日期不早于开始日期。使用相同的开始日期和结束日期将生成0。假期不在考虑之列。

这与Rodger的解决方案之间的主要区别是，矩阵和结果数字串是由复杂的算法构建的，我没有包括在内。此算法的输出通过单元测试进行验证(请参阅下面的测试输入和输出)。在矩阵中，任何给定的x和y值对的交集(WEEKDAY(@S)和WEEKDAY(@E) )产生两个值之间的工作日差。赋值顺序实际上并不重要，因为两者相加是为了绘制位置。

工作日是星期一到星期五。

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 5 5
T| 5 0 1 2 3 4 4
W| 4 5 0 1 2 3 3
T| 3 4 5 0 1 2 2
F| 2 3 4 5 0 1 1
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 5 0

表中的49个值连接成以下字符串：

0123455501234445012333450122234501101234000123450

最后，正确的表达式是：

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

我已经使用此解决方案验证了以下输入和输出：

Sunday, 2012-08-26 -> Monday, 2012-08-27 = 0
Sunday, 2012-08-26 -> Sunday, 2012-09-02 = 5
Monday, 2012-08-27 -> Tuesday, 2012-08-28 = 1
Monday, 2012-08-27 -> Monday, 2012-09-10 = 10
Monday, 2012-08-27 -> Monday, 2012-09-17 = 15
Monday, 2012-08-27 -> Tuesday, 2012-09-18 = 16
Monday, 2012-08-27 -> Monday, 2012-09-24 = 20
Monday, 2012-08-27 -> Monday, 2012-10-01 = 25
Tuesday, 2012-08-28 -> Wednesday, 2012-08-29 = 1
Wednesday, 2012-08-29 -> Thursday, 2012-08-30 = 1
Thursday, 2012-08-30 -> Friday, 2012-08-31 = 1
Friday, 2012-08-31 -> Saturday, 2012-09-01 = 1
Saturday, 2012-09-01 -> Sunday, 2012-09-02 = 0
Sunday, 2012-09-02 -> Monday, 2012-09-03 = 0
Monday, 2012-09-03 -> Tuesday, 2012-09-04 = 1
Tuesday, 2012-09-04 -> Wednesday, 2012-09-05 = 1
Wednesday, 2012-09-05 -> Thursday, 2012-09-06 = 1
Thursday, 2012-09-06 -> Friday, 2012-09-07 = 1
Friday, 2012-09-07 -> Saturday, 2012-09-08 = 1
Saturday, 2012-09-08 -> Sunday, 2012-09-09 = 0
Monday, 2012-09-24 -> Sunday, 2012-10-07 = 10
Saturday, 2012-08-25 -> Saturday, 2012-08-25 = 0
Saturday, 2012-08-25 -> Sunday, 2012-08-26 = 0
Saturday, 2012-08-25 -> Monday, 2012-08-27 = 0
Saturday, 2012-08-25 -> Tuesday, 2012-08-28 = 1
Saturday, 2012-08-25 -> Wednesday, 2012-08-29 = 2
Saturday, 2012-08-25 -> Thursday, 2012-08-30 = 3
Saturday, 2012-08-25 -> Friday, 2012-08-31 = 4
Saturday, 2012-08-25 -> Sunday, 2012-09-02 = 0
Monday, 2012-08-27 -> Monday, 2012-08-27 = 0
Monday, 2012-08-27 -> Tuesday, 2012-08-28 = 1
Monday, 2012-08-27 -> Wednesday, 2012-08-29 = 2
Monday, 2012-08-27 -> Thursday, 2012-08-30 = 3
Monday, 2012-08-27 -> Friday, 2012-08-31 = 4
Monday, 2012-08-27 -> Saturday, 2012-09-01 = 5
Monday, 2012-08-27 -> Sunday, 2012-09-02 = 5

Answer 3

提议的字符串可能是错误的吗？

DATEDIFF(from，to)不包括'to‘。以同样的方式，这个字符串也应该这样：

星期一->星期五={星期一，星期二，星期三，星期四}=4

星期一->星期六={星期一，星期二，星期三，星期四，星期五}=5

星期二->星期一= {Tu，Wed，Th，Fri，skip Sat，skip Sun，Mon is excluded} =4

诸若此类

建议的矩阵：

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 5 5
T| 4 0 1 2 3 4 4
W| 3 4 0 1 2 3 3
T| 2 3 4 0 1 2 2
F| 1 2 3 4 0 1 1
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 5 0

字符串：'0123455401234434012332340122123401101234000123450‘

我是不是错过了什么？:)