Calculate working hours with dynamic week numbers for a work schedule

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

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 table 2

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

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

Solution

You can use a LATERAL join and conditional aggregation to generate the hours per week and then PIVOT to get the values as columns:

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

Which, for your sample 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
);

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

Note: You appear to have employee_number and employee_type in the wrong order in your final INSERT.

Outputs:

EMPLOYEE_NUMBER	FIRST_NAME	LAST_NAME	WK1	WK2	WK3	WK4	WK5	WK6	WK7
1000599	Sead	Babahmetovic	40	40	16	16	40	40	40

fiddle