如何用Oracle SQL计算员工各季度工作天数（不使用PL/SQL）

需求说明 #

编写Oracle SQL语句，输入起止日期（示例：2023/1/1-2023/6/20），从员工任职时间表中统计员工在对应季度的活跃天数，输出指定格式的结果，且不使用PL/SQL。

原始表数据 #

employee  date_from  date_to   
x         1/1/2023   2/9/2023
y         10/1/2023  3/30/2023 

预期输出（中文翻译版） #

employee  date_from  date_to       year   quarter                   comments
X         1/1/2023   2/9/2023     2023      q1         员工X本季度活跃40天，季度总天数90天。
y         10/1/2023  3/30/2023    2022      q4         员工y本季度活跃90天，季度总天数90天。
y         10/1/2023  3/30/2023    2023      q1         员工y本季度活跃90天，季度总天数90天。

纯SQL实现方案 #

可以完全用纯SQL实现，核心是生成目标日期范围内的季度列表，再关联员工任职数据计算重叠天数。以下是完整SQL代码：

WITH input_dates AS (
    -- 定义输入的起止日期，可替换为绑定变量
    SELECT TO_DATE('2023/1/1', 'YYYY/MM/DD') AS start_date,
           TO_DATE('2023/6/20', 'YYYY/MM/DD') AS end_date
    FROM DUAL
),
quarters AS (
    -- 生成输入日期范围内的所有季度信息
    SELECT DISTINCT
           EXTRACT(YEAR FROM TRUNC(q_date, 'Q')) AS year,
           'q' || TO_CHAR(TRUNC(q_date, 'Q'), 'Q') AS quarter,
           TRUNC(q_date, 'Q') AS q_start,
           ADD_MONTHS(TRUNC(q_date, 'Q'), 3) - 1 AS q_end
    FROM input_dates,
         -- 生成覆盖日期范围的所有日期，用于提取季度
         (SELECT start_date + LEVEL - 1 AS q_date
          FROM input_dates
          CONNECT BY LEVEL <= (end_date - start_date) + 1)
)
SELECT
    e.employee,
    e.date_from,
    e.date_to,
    q.year,
    q.quarter,
    '员工' || e.employee || '本季度活跃' || 
        CASE 
            WHEN GREATEST(e.date_from, q.q_start) <= LEAST(e.date_to, q.q_end)
            THEN LEAST(e.date_to, q.q_end) - GREATEST(e.date_from, q.q_start) + 1
            ELSE 0
        END || '天，季度总天数' || (q.q_end - q.q_start + 1) || '天。' AS comments
FROM employee_tenure e
JOIN quarters q
    -- 关联条件：员工任职区间与季度存在重叠
    ON e.date_from <= q.q_end
    AND e.date_to >= q.q_start
-- 过滤与输入日期范围无关的任职记录
WHERE e.date_from <= (SELECT end_date FROM input_dates)
  AND e.date_to >= (SELECT start_date FROM input_dates)
ORDER BY e.employee, q.year, q.quarter;

代码说明 #

1. input_dates：定义查询的起止日期，实际使用时可改为绑定变量（如:p_start_date），方便动态传入参数。
2. quarters：通过CONNECT BY生成输入范围内的所有日期，再提取每个日期对应的季度起止信息，去重后得到所有涉及的季度。
3. 主查询：关联员工任职表和季度表，用GREATEST和LEAST计算两个区间的重叠部分，进而得到活跃天数；最后拼接成要求的注释文本，按员工和季度排序输出。

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