我想查看当前和未来几周每个员工的可用小时数。Wk1（第1周）是当前一周。结果如下所示：
enter image description here
我有一个表1，其中有缺勤时间，我有一个表2，其中有员工的工作计划（大多数是每周40小时，但有时也在周末工作）：
table 1table 2
我现在有以下查询：

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
;

结果是：
enter image description here
我需要一些帮助，一个好的解决方案。我最好的猜测是创建7与作为条款的不同周，并加入他们的缺席周。但在我把这个小时的工作，我想知道如果我在正确的方向思考。
我试图透视我已经得到的结果，但我偶然发现这样一个事实，即你需要在透视中使用静态数据，这样做是行不通的。
要使用数据创建表1和表2，请执行以下操作：

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
);

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')
);