Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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
);
1 rows affected

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')
);
1 rows affected

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


EMPLOYEE_NUMBER
FIRST_NAME
LAST_NAME
WK1
WK2
WK3
WK4
WK5
WK6
WK7


1000599
Sead
Babahmetovic
40
40
16
16
40
40
40