我开始了一个人力资源管理项目,我想数数两个日期之间的天数,而不计算假日和周末。这样人力资源部就可以计算员工休息日了
这里是这样的情况,我想在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天。这意味着查询只排除周末,而不排除假日。
我的问题出什么问题了?我是不是遗漏了什么?谢谢你对我的照顾
发布于 2018-02-08 22:59:41
我找到了这个问题的答案
结果,我只需要使用一个简单的算术运算符来解决这个问题。
SELECT (SELECT DATEDIFF('2018-02-20', '2018-02-14')) - (SELECT COUNT(id) FROM tbl_holiday WHERE dates BETWEEN '2018-02-14' AND '2018-02-20');发布于 2018-02-08 14:09:22
@RichardDoe,来自问题评论。
在一个日期表的合理实现中,您创建了一个所有日期的列表(覆盖足够的范围来处理您可能针对它运行的任何查询-从今天起每一种方式都可能是有用的最少时间),并且每天都存储各种派生属性。
我最近用一些基本的工具编写了一个Q&A,可以让您在Server中入门:https://stackoverflow.com/a/48611348/9129668
不幸的是,我没有一个MySQL环境,也不太熟悉它,不允许我在头上编写或调整查询(就像我在这里所做的那样),但我希望这将用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中,因此对数据库运行的任何查询都可以访问公司工作日的预定义列表。然后,针对它运行的查询开始类似于简单的英语(而不是您在问题中链接和修改的方法,这种方法很巧妙,但还不清楚)。
如果您希望获得最佳性能(如果您正在大量执行这些计算,这将是相关的),那么您将定义适当的参数,将该批保存到存储的日期表中,并对该表进行适当的索引。这样,您的查询将变得像查询的最后一部分一样简单,但是引用存储的日期表而不是with-块。
我在对您的问题的评论中提到的顺序编号的工作日,是提高对日期表的某些类型查询的效率和可索引性的又一步,但我现在不会进一步复杂化这个答案。如需进一步澄清,请随时询问。
https://stackoverflow.com/questions/48682162
复制相似问题