mysql - 如何计算除假期和周末外的两个日期之间的天数？

Question

我开始了一个人力资源管理项目，我想计算 2 个日期之间的天数，而不计算假期和周末。这样HR就可以统计员工的休息日了

情况是这样的，我要算在2018-02-14之间和 2018-02-20 2018-02-16哪里有办公室假期.结果应该是 3 天。

我已经创建了一个名为 tbl_holiday 的表我把一年中所有的周末和假期都放在那里

I found this post , 我在我的 MariaDB 上试过了

这是我的查询:

SELECT 5 * (DATEDIFF('2018-02-20', '2018-02-14') DIV 7) + 
MID('0123444401233334012222340111123400012345001234550', 7 * 
WEEKDAY('2018-02-14') + WEEKDAY('2018-02-20') + 1, 1) - 
(SELECT COUNT(dates) FROM tbl_holiday WHERE dates NOT IN (SELECT dates FROM tbl_holiday)) as Days

查询有效，但结果是 4 天，而不是 3 天。这意味着查询只排除周末而不排除假期

我的查询有什么问题？我错过了什么吗？谢谢你帮助我

Answer 1

@RichardDoe，来自问题评论。

在日期表的合理实现中，您创建了一个包含所有 天的列表(涵盖足够的范围以应对您可能针对它运行的任何查询 - 从今天开始的每一种方式可能是 15 年一个有用的最小值)，并且每天都会存储各种派生属性。

我最近写了一个问答，其中包含可以帮助您开始使用 SQL Server 的基本工具:https://stackoverflow.com/a/48611348/9129668

不幸的是，我没有 MySQL 环境或对它非常熟悉，无法凭空编写或调整查询(就像我在这里所做的那样)，但我希望这将说明一个SQL Server 语法中的解决方案。

根据我链接到的答案(动态生成一个日期表)并通过添加您的假期表来扩展它(并对您如何定义假期表做出一些推断)，并指出工作日是周一至周五的任何一天，不是假期，您可以编写这样的查询来获取任意两个日期之间的工作日数:

WITH 
dynamic_date_table AS
(
    SELECT *
    FROM generate_series_datetime2('2000-01-01','2030-12-31',1)
    CROSS APPLY datetime2_params_fxn(datetime2_value)
)
,date_table_ext1 AS
(
    SELECT
        ddt.*
        ,IIF(hol.dates IS NOT NULL, 1, 0) AS is_company_holiday

    FROM 
        dynamic_date_table AS ddt

    LEFT JOIN 
        tbl_holiday AS hol
        ON (hol.dates = ddt.datetime2_value)
)
,date_table_ext2 AS
(
    SELECT
        *
        ,IIF(is_weekend = 1 OR is_company_holiday = 1, 0, 1) AS is_company_work_day
    FROM date_table_ext1
)

SELECT 
    COUNT(datetime2_value)
FROM 
    date_table_ext2
WHERE 
    (datetime2_value BETWEEN '2018-02-14' AND '2018-02-20')
    AND
    (is_company_work_day = 1)

显然，一个结构良好的解决方案的想法是将这些中间计算(对整个公司来说是通用的)纳入 date_params_fxn，这样任何查询都会针对数据库 yield 运行访问公司工作日的预定义列表。针对它运行的查询然后开始类似于简单的英语(而不是您链接到并在您的问题中采用的方法，这种方法很巧妙但远非清晰)。

如果您想要最佳性能(如果您大量使用这些计算，这将是相关的)，那么您可以定义适当的参数，将批处理保存到存储日期表中，并适本地对该表进行索引。这样，您的查询将变得与此处查询的最后部分一样简单，但引用存储的日期表而不是 with block 。

我在对你的问题的评论中提到的按顺序编号的工作日，是针对日期表的某些类型查询的效率和可索引性的又一步，但我现在不会进一步复杂化这个答案。如果需要任何进一步的说明，请随时询问。