基于动态周数计算员工可用工时的SQL方案咨询

需求说明 #

获取当前及未来7周（Wk1为当前周）工厂钳工的可用工时概览，期望结果为每周作为列的布局形式。

现有数据 #

• employee_schedule表：存储员工排班方案（多为每周40小时，支持周末工时）
• work_orders表：存储员工缺勤时段

当前实现及问题 #

已编写Oracle SQL查询，返回每行对应一周的工时数据，但不符合期望的列式布局；尝试使用PIVOT实现行转列，因需静态列定义失败。目前考虑创建7个WITH子句关联缺勤周来实现，咨询该思路是否可行，或寻求更优方案。

当前SQL查询 #

WITH WEEK AS ( 
    SELECT  TO_CHAR(TRUNC(SYSDATE, 'IW') + (level - 1) * 7, 'IW') AS week_number
    , TRUNC(SYSDATE, 'IW') + (level - 1) * 7 AS week_start
    , TRUNC(SYSDATE, 'IW') + level * 7 - 1 AS week_end
    FROM DUAL
    WHERE level <= 7
    CONNECT BY TRUNC(SYSDATE, 'IW') + (level - 1) * 7 <= SYSDATE + 49
    ) 
    
, ABSENCE AS (
    SELECT  EMP_P.EMPLOYEE_NUMBER
    , EMP_P.START_DATE AS START_DATE_ABSENCE
    , EMP_P.END_DATE AS END_DATE_ABSENCE
    , sum(TOTAL_ABSENCE_HOURS_PER_WEEK) AS ABSENCE_HOURS
    , WEEK_NUMBER
    FROM XXAS.XXAS_FHT_EMP_PERIODS_R EMP_P
    JOIN XXAS.XXAS_FHT_EMPLOYEES_ALL_MV EMP_A
      ON EMP_A.EMPLOYEE_NUMBER = EMP_P.EMPLOYEE_NUMBER
    CROSS APPLY (
        SELECT  TO_CHAR((EMP_P.START_DATE + LEVEL - 1), 'IW') AS WEEK_NUMBER
        ,(
              CASE to_number(to_char((EMP_P.START_DATE + LEVEL - 1),'D'))
              WHEN 1 THEN EMP_A.MONDAY
              WHEN 2 THEN EMP_A.TUESDAY
              WHEN 3 THEN EMP_A.WEDNESDAY
              WHEN 4 THEN EMP_A.THURSDAY
              WHEN 5 THEN EMP_A.FRIDAY
              WHEN 6 THEN EMP_A.SATURDAY
              WHEN 7 THEN EMP_A.SUNDAY
              END
            ) AS TOTAL_ABSENCE_HOURS_PER_WEEK
        FROM DUAL
        CONNECT BY EMP_P.START_DATE + LEVEL - 1 <= EMP_P.END_DATE
        )
    WHERE EMP_A.EMPLOYEE_TYPE = 'Factory'
    AND EMP_A.FUNCTION = 'Fitter'
    AND (EMP_A.EFFECTIVE_END_DATE >= SYSDATE
        OR EMP_A.EFFECTIVE_END_DATE IS NULL)
    AND EMP_P.START_DATE >= SYSDATE
        
    GROUP BY EMP_P.EMPLOYEE_NUMBER
    , WEEK_NUMBER
    , EMP_P.START_DATE
    , EMP_P.END_DATE
    
    
)

SELECT EMP_A.FULL_NAME
, EMP_A.EMPLOYEE_NUMBER
, WK.week_number
, WK.week_start
, WK.week_end 
, SUM(EMP_A.monday + EMP_A.tuesday + EMP_A.wednesday + EMP_A.thursday + EMP_A.friday + EMP_A.saturday + EMP_A.sunday) AS WORK_HOURS
, A.ABSENCE_HOURS
, NVL((SUM(EMP_A.monday + EMP_A.tuesday + EMP_A.wednesday + EMP_A.thursday + EMP_A.friday + EMP_A.saturday + EMP_A.sunday) - A.ABSENCE_HOURS)
       ,SUM(EMP_A.monday + EMP_A.tuesday + EMP_A.wednesday + EMP_A.thursday + EMP_A.friday + EMP_A.saturday + EMP_A.sunday)) AS AVAILABLE_HOURS
,
case
    when (
        NVL((SUM(EMP_A.monday + EMP_A.tuesday + EMP_A.wednesday + EMP_A.thursday + EMP_A.friday + EMP_A.saturday + EMP_A.sunday) - A.ABSENCE_HOURS)
       ,SUM(EMP_A.monday + EMP_A.tuesday + EMP_A.wednesday + EMP_A.thursday + EMP_A.friday + EMP_A.saturday + EMP_A.sunday))
        ) 
        <
        (
        SUM(EMP_A.monday + EMP_A.tuesday + EMP_A.wednesday + EMP_A.thursday + EMP_A.friday + EMP_A.saturday + EMP_A.sunday)
        ) then 'red'
    else 'green'
end as field_color
FROM xxas.XXAS_FHT_EMPLOYEES_ALL_MV EMP_A

LEFT OUTER JOIN XXAS.XXAS_FHT_EMP_PERIODS_R EMP_P
ON EMP_P.EMPLOYEE_NUMBER = EMP_A.EMPLOYEE_NUMBER
AND EMP_P.WORK_ORDER_NAME = 'Leave or absence'
AND EMP_P.END_DATE >= TRUNC(SYSDATE, 'IW')

CROSS JOIN WEEK WK

LEFT OUTER JOIN ABSENCE A
  ON A.EMPLOYEE_NUMBER = EMP_A.EMPLOYEE_NUMBER
 AND A.WEEK_NUMBER = WK.WEEK_NUMBER

WHERE EMP_A.EMPLOYEE_TYPE = 'Factory'
 AND EMP_A.FUNCTION = 'Fitter'
 AND (EMP_A.EFFECTIVE_END_DATE >= SYSDATE
      OR EMP_A.EFFECTIVE_END_DATE IS NULL
      )
      
 AND EMP_A.EMPLOYEE_NUMBER = '1000599'

GROUP BY EMP_A.EMPLOYEE_NUMBER 
, WK.WEEK_NUMBER   
, WK.week_start
, WK.week_end
, EMP_A.EMPLOYEE_NUMBER
, EMP_A.FULL_NAME
, EMP_P.START_DATE
, EMP_P.END_DATE
, A.ABSENCE_HOURS

ORDER BY WK.week_number
;

建表语句 #

employee_schedule表

CREATE TABLE employee_schedule (
  employee_number VARCHAR2(50),
  person_id NUMBER,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  function VARCHAR2(50),
  employee_type VARCHAR2(50),
  employment_start_date DATE,
  monday NUMBER,
  tuesday NUMBER,
  wednesday NUMBER,
  thursday NUMBER,
  friday NUMBER,
  saturday NUMBER,
  sunday NUMBER
);
INSERT INTO employee_schedule (
  employee_number, person_id, first_name, last_name, function, employee_type, employment_start_date, monday, tuesday, wednesday, thursday, friday, saturday, sunday
) VALUES (
  '1000599', 43010, 'Sead', 'Babahmetovic', 'Fitter', 'Factory', TO_DATE('01-01-2021 00:00:00', 'MM-DD-YYYY HH24:MI:SS'), 8, 8, 8, 8, 8, 0, 0
);

work_orders表

CREATE TABLE work_orders (
  employee_number VARCHAR2(50),
  employee_type VARCHAR2(50),
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  work_order_name VARCHAR2(100),
  start_date DATE,
  end_date DATE
);

INSERT INTO work_orders (
  employee_number, employee_type, first_name, last_name, work_order_name, start_date, end_date
) VALUES (
  '43010', '1000599', 'Sead', 'Babahmetovic', 'Leave or absence', TO_DATE('26-04-2023 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), TO_DATE('03-05-2023 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
);

解决方案建议 #

思路：固定周标识+静态PIVOT

因为周数固定为7周（当前+未来6周），可以先将动态周数映射为WK1到WK7的静态标签，再用PIVOT实现行转列，无需动态SQL即可满足需求。

优化后的SQL示例

WITH WEEK_DETAILS AS (
    -- 生成7周数据并标记为WK1-WK7
    SELECT 
        'WK' || LEVEL AS week_label,
        TO_CHAR(TRUNC(SYSDATE, 'IW') + (LEVEL - 1)*7, 'IW') AS week_number,
        TRUNC(SYSDATE, 'IW') + (LEVEL - 1)*7 AS week_start,
        TRUNC(SYSDATE, 'IW') + LEVEL*7 - 1 AS week_end
    FROM DUAL
    CONNECT BY LEVEL <=7
),
EMPLOYEE_BASE AS (
    -- 获取钳工基础排班数据
    SELECT 
        employee_number,
        first_name || ' ' || last_name AS full_name,
        monday + tuesday + wednesday + thursday + friday + saturday + sunday AS weekly_work_hours
    FROM employee_schedule
    WHERE employee_type = 'Factory'
      AND function = 'Fitter'
      AND employment_start_date <= SYSDATE
),
ABSENCE_CALC AS (
    -- 计算员工每周缺勤工时
    SELECT 
        wo.employee_number,
        wd.week_label,
        SUM(
            CASE TO_CHAR(wo.start_date + LEVEL -1, 'D')
                WHEN '1' THEN es.monday
                WHEN '2' THEN es.tuesday
                WHEN '3' THEN es.wednesday
                WHEN '4' THEN es.thursday
                WHEN '5' THEN es.friday
                WHEN '6' THEN es.saturday
                WHEN '7' THEN es.sunday
            END
        ) AS absence_hours
    FROM work_orders wo
    JOIN employee_schedule es ON wo.employee_number = es.person_id
    JOIN WEEK_DETAILS wd 
        ON wo.start_date <= wd.week_end 
        AND wo.end_date >= wd.week_start
    CONNECT BY LEVEL <= (wo.end_date - wo.start_date +1)
    WHERE wo.work_order_name = 'Leave or absence'
      AND es.employee_type = 'Factory'
      AND es.function = 'Fitter'
    GROUP BY wo.employee_number, wd.week_label
),
EMPLOYEE_WEEK_DATA AS (
    -- 关联基础数据与缺勤数据，计算可用工时
    SELECT 
        eb.employee_number,
        eb.full_name,
        wd.week_label,
        eb.weekly_work_hours,
        NVL(ac.absence_hours, 0) AS absence_hours,
        eb.weekly_work_hours - NVL(ac.absence_hours, 0) AS available_hours,
        CASE 
            WHEN eb.weekly_work_hours - NVL(ac.absence_hours, 0) < eb.weekly_work_hours THEN 'red'
            ELSE 'green'
        END AS field_color
    FROM EMPLOYEE_BASE eb
    CROSS JOIN WEEK_DETAILS wd
    LEFT JOIN ABSENCE_CALC ac 
        ON eb.employee_number = ac.employee_number 
        AND wd.week_label = ac.week_label
    WHERE eb.employee_number = '1000599' -- 移除该条件可查询所有钳工
)
-- PIVOT转换为列式布局
SELECT 
    employee_number,
    full_name,
    MAX(CASE WHEN week_label = 'WK1' THEN available_hours END) AS WK1_可用工时,
    MAX(CASE WHEN week_label = 'WK1' THEN field_color END) AS WK1_状态,
    MAX(CASE WHEN week_label = 'WK2' THEN available_hours END) AS WK2_可用工时,
    MAX(CASE WHEN week_label = 'WK2' THEN field_color END) AS WK2_状态,
    MAX(CASE WHEN week_label = 'WK3' THEN available_hours END) AS WK3_可用工时,
    MAX(CASE WHEN week_label = 'WK3' THEN field_color END) AS WK3_状态,
    MAX(CASE WHEN week_label = 'WK4' THEN available_hours END) AS WK4_可用工时,
    MAX(CASE WHEN week_label = 'WK4' THEN field_color END) AS WK4_状态,
    MAX(CASE WHEN week_label = 'WK5' THEN available_hours END) AS WK5_可用工时,
    MAX(CASE WHEN week_label = 'WK5' THEN field_color END) AS WK5_状态,
    MAX(CASE WHEN week_label = 'WK6' THEN available_hours END) AS WK6_可用工时,
    MAX(CASE WHEN week_label = 'WK6' THEN field_color END) AS WK6_状态,
    MAX(CASE WHEN week_label = 'WK7' THEN available_hours END) AS WK7_可用工时,
    MAX(CASE WHEN week_label = 'WK7' THEN field_color END) AS WK7_状态
FROM EMPLOYEE_WEEK_DATA
GROUP BY employee_number, full_name;

说明

1. 固定周标识：将动态周数转换为WK1到WK7的静态标签，解决PIVOT需要静态列的限制。
2. 简化缺勤计算：通过CONNECT BY生成缺勤日期范围，结合排班表计算每日缺勤工时后按周汇总。
3. 灵活扩展：PIVOT部分可根据需求调整展示字段，比如同时显示每周总工时、缺勤工时等。

内容的提问来源于stack exchange，提问作者Gerard van der Schoot