问题

以下是代码的翻译部分：

我想要获取当前和接下来几周每位员工的可用工作时间总览。Wk1（第1周）是当前周。结果应该如下所示：

我有一个表格1，其中记录了缺勤时段，还有一个表格2，记录了员工的工作计划（通常每周工作40小时，但有时也包括周末工作时间）：

[表格1](https://i.stack.imgur.com/O9pDu.png)
[表格2](https://i.stack.imgur.com/DQQZ8.png)

我现在有以下查询：

（以下为SQL查询的翻译部分）

根据我的最佳猜测，我需要创建7个不同周的`WITH`子句，并将它们与缺勤周进行连接。但在我投入大量工作之前，我想知道我是否走在正确的方向上。

我尝试过对我已经有的结果进行数据透视，但我遇到了需要在透视中使用静态数据的问题，所以这不起作用。

要创建表1和表2的数据，您可以使用以下SQL代码：

（以下为创建表1和表2的数据的翻译部分）

希望这能帮助您。如果您有任何其他问题，请随时提问。

<details>
<summary>英文:</summary>

I would like to have an overview of the available hours per employee for the current and coming weeks. Wk1 (week 1) is the current week. The result would be like this:

[enter image description here](https://i.stack.imgur.com/7lfEX.png)

I have a table 1 where the absence periods are available and I have table 2 with the working scheme of the employee (mostly 40 hours per week but sometimes also working hours in the weekend):

[table 1](https://i.stack.imgur.com/O9pDu.png) 
[table 2](https://i.stack.imgur.com/DQQZ8.png)


What I do have now is the following query:

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
;

which results in this:

[enter image description here](https://i.stack.imgur.com/rxD9m.png)


I need some help with a good solution. My best guess is to create 7 with as clauses for the different weeks and join them on absence weeks. But before I put hours of work in this I would like to know if I am thinking in the right direction.


I tried to pivot the result that I already have. But I stumbled upon the fact that you need static data in the pivot so that won't work.


To create tables 1 and 2 with the data:

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

</details>


# 答案1
**得分**: 0

你可以使用`LATERAL`连接和条件聚合来生成每周的工作小时数，然后使用`PIVOT`将这些值作为列获取：

```lang-sql
WITH weeks AS ( 
  SELECT LEVEL 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
),
worked_hours (week_number, employee_number, first_name, last_name, hours) AS (
  SELECT w.week_number,
         s.employee_number,
         s.first_name,
         s.last_name,
         s.monday    * (1 - a.monday)
         + s.tuesday   * (1 - a.tuesday)
         + s.wednesday * (1 - a.wednesday)
         + s.thursday  * (1 - a.thursday)
         + s.friday    * (1 - a.friday)
         + s.saturday  * (1 - a.saturday)
         + s.sunday    * (1 - a.sunday)
  FROM   weeks w
         CROSS JOIN employee_schedule s
         LEFT OUTER JOIN LATERAL (
           SELECT LEAST(COUNT(CASE WHEN w.week_start + 0 BETWEEN o.start_date AND o.end_date THEN 1 END), 1) AS monday,
                  LEAST(COUNT(CASE WHEN w.week_start + 1 BETWEEN o.start_date AND o.end_date THEN 1 END), 1) AS tuesday,
                  LEAST(COUNT(CASE WHEN w.week_start + 2 BETWEEN o.start_date AND o.end_date THEN 1 END), 1) AS wednesday,
                  LEAST(COUNT(CASE WHEN w.week_start + 3 BETWEEN o.start_date AND o.end_date THEN 1 END), 1) AS thursday,
                  LEAST(COUNT(CASE WHEN w.week_start + 4 BETWEEN o.start_date AND o.end_date THEN 1 END), 1) AS friday,
                  LEAST(COUNT(CASE WHEN w.week_start + 5 BETWEEN o.start_date AND o.end_date THEN 1 END), 1) AS saturday,
                  LEAST(COUNT(CASE WHEN w.week_start + 6 BETWEEN o.start_date AND o.end_date THEN 1 END), 1) AS sunday
           FROM   work_orders o
           WHERE  o.employee_number = s.employee_number
           AND    o.start_date <= w.week_end
           AND    o.end_date   >= w.week_start
        ) a
        ON (1 = 1)
)
SELECT *
FROM   worked_hours
PIVOT (
  SUM(hours) FOR week_number IN (
    1 AS wk1,
    2 AS wk2,
    3 AS wk3,
    4 AS wk4,
    5 AS wk5,
    6 AS wk6,
    7 AS wk7
  )
);

对于你的示例数据：

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

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

INSERT INTO work_orders (
  employee_number, employee_type, first_name, last_name, work_order_name, start_date, end_date
) VALUES (
  '1000599', '43010', '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')
);

注意：你的最后一个INSERT中似乎employee_number和employee_type的顺序有问题。

输出结果：

fiddle

WITH weeks AS ( 
  SELECT LEVEL 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
),
worked_hours (week_number, employee_number, first_name, last_name, hours) AS (
  SELECT w.week_number,
         s.employee_number,
         s.first_name,
         s.last_name,
         s.monday    * (1 - a.monday)
         + s.tuesday   * (1 - a.tuesday)
         + s.wednesday * (1 - a.wednesday)
         + s.thursday  * (1 - a.thursday)
         + s.friday    * (1 - a.friday)
         + s.saturday  * (1 - a.saturday)
         + s.sunday    * (1 - a.sunday)
  FROM   weeks w
         CROSS JOIN employee_schedule s
         LEFT OUTER JOIN LATERAL (
           SELECT LEAST(COUNT(CASE WHEN w.week_start + 0 BETWEEN o.start_date AND o.end_date THEN 1 END), 1) AS monday,
                  LEAST(COUNT(CASE WHEN w.week_start + 1 BETWEEN o.start_date AND o.end_date THEN 1 END), 1) AS tuesday,
                  LEAST(COUNT(CASE WHEN w.week_start + 2 BETWEEN o.start_date AND o.end_date THEN 1 END), 1) AS wednesday,
                  LEAST(COUNT(CASE WHEN w.week_start + 3 BETWEEN o.start_date AND o.end_date THEN 1 END), 1) AS thursday,
                  LEAST(COUNT(CASE WHEN w.week_start + 4 BETWEEN o.start_date AND o.end_date THEN 1 END), 1) AS friday,
                  LEAST(COUNT(CASE WHEN w.week_start + 5 BETWEEN o.start_date AND o.end_date THEN 1 END), 1) AS saturday,
                  LEAST(COUNT(CASE WHEN w.week_start + 6 BETWEEN o.start_date AND o.end_date THEN 1 END), 1) AS sunday
           FROM   work_orders o
           WHERE  o.employee_number = s.employee_number
           AND    o.start_date <= w.week_end
           AND    o.end_date   >= w.week_start
        ) a
        ON (1 = 1)
)
SELECT *
FROM   worked_hours
PIVOT (
  SUM(hours) FOR week_number IN (
    1 AS wk1,
    2 AS wk2,
    3 AS wk3,
    4 AS wk4,
    5 AS wk5,
    6 AS wk6,
    7 AS wk7
  )
);

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

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

INSERT INTO work_orders (
  employee_number, employee_type, first_name, last_name, work_order_name, start_date, end_date
) VALUES (
  '1000599', '43010', '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')
);