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