如何在MS Access中匹配日期区间交集并统计每日缺勤员工数

核心逻辑 #

要输出每一个被休假区间覆盖的自然日、以及当日休假总人数，核心是先凑出覆盖全周期的连续日期基准，再把每个日期和休假记录做匹配计数。因为Access没有内置的递归查询能力，没法直接把每个休假区间拆成单天，所以必须先搭好日期基准再做关联统计。

具体操作步骤 #

1. 前置数据校验 #

首先必须确认：表里的begin、end字段是真日期类型，不是存了类似1.3.2021.格式的文本。如果是文本存储，所有日期比较都会出错，需要先在查询里用CDate(Replace([begin], ".", "/"))、CDate(Replace([end], ".", "/"))转成标准日期格式再用。
特别注意：end是Access的保留关键字，写SQL的时候一定要加方括号[end]，不然会报语法错误

2. 生成连续自然日期基准 #

没有现成日期表的情况下，可以用笛卡尔积拼接的方式快速生成0-999的连续数字，再通过日期加减凑出从最早休假开始日到最晚休假结束日的所有连续日期：

-- 第一步：生成0-999的连续数字序列（足够覆盖2-3年的日期跨度）
SELECT DISTINCT 
  a.i + b.i*10 + c.i*100 AS num
FROM 
  (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
   UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a,
  (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
   UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b,
  (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
   UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c;

把上面的查询存为qryNum，再写第二个查询生成全量连续日期：

-- 第二步：生成从最早休假开始日到最晚休假结束日的所有连续自然日期
SELECT 
  DateAdd("d", q.num, r.min_begin) AS stat_date
FROM 
  qryNum AS q,
  (SELECT Min([begin]) AS min_begin, Max([end]) AS max_end FROM 你的休假表名) AS r
WHERE 
  q.num <= DateDiff("d", r.min_begin, r.max_end);

把这个查询存为qryDateList。

3. 关联计数得到最终结果 #

把日期列表和休假表左关联，只要日期落在某个休假的起止区间内，就算入当日的休假人数，最后按日期分组统计，过滤掉没有任何人休假的空档日期：

SELECT 
  Format(d.stat_date, "d.m.yyyy.") AS 自然日期,
  COUNT(t.Name) AS 当日休假总人数
FROM 
  qryDateList AS d
LEFT JOIN 
  你的休假表名 AS t
ON 
  d.stat_date BETWEEN t.[begin] AND t.[end]
GROUP BY 
  d.stat_date
HAVING COUNT(t.Name) > 0 -- 只保留有休假记录的日期，匹配需求里“被休假区间覆盖”的范围要求
ORDER BY 
  d.stat_date;

常见问题提醒 #

• 用BETWEEN匹配的时候是闭区间，刚好能覆盖那种当天开始当天结束的短休假（比如Robert 4月2日的单日休假），不用额外调整匹配逻辑
• 如果经常要跑这类统计，建议直接建一张永久的日期维度表，存上从最早业务日期到未来10年的所有连续日期，比每次临时生成序列的查询效率高很多
• 输出的时候用Format函数把日期格式调整成需要的d.m.yyyy.样式，和示例的输出格式完全一致

内容的提问来源于stack exchange，提问作者Mario