Need to get duty time and accumulated working hours.
Have two tables employee and working hours.
calculate duty time as work_end_date_time – work_start_date_time and
calculate accumulated hours by adding duty_hour based on input date passed
employee table
empid workid
1 6
2 7
3 8
wroking_hours table
workid work_start_date_time work_end_date_time
6 1-sep-2024 9:00 1-sep-2024 20:00
6 2-sep-2024 22:00 3-sep-2024 6:00
6 4-sep-2024 6:00 4-sep-2024 10:00
Output table should be like (if I pass 4-sep-2024 as base date then)
empid accumulation_date duty_time accumulated_working_hours
1 4-sep-2024 4:00 4:00
1 3-sep-2024 0:00 4:00
1 2-sep-2024 8:00 12:00
1 1-sep-2024 11:00 21:00
Here is my basic query and what I have tried,
Select
a. empid ,
b.work_start_date_time ,
b.work_end_date_time,
FLOOR((b.work_end_date_time-b.work_start_date_time)*24) + ((b.work_end_date_time-b.work_start_date_time)*24-FLOOR((b.work_end_date_time-b.work_start_date_time)*24))/100*60 as duty_time
--SUM(duty_time) over() as accumulated_duty_hours
from
employee a, wroking_hours b
where
a.empid=1 and
a.workid = b.workid and
b.work_start_date_time BETWEEN TO_DATE ('2024-09-01', 'YYYY-MM-DD') AND TO_DATE('2024-09-5', 'YYYY-MM-DD')
1
You can use a row-generator to create a calendar and then join your table to it and total the hours:
WITH bounds (start_date, end_date) AS (
SELECT DATE '2024-09-01', DATE '2024-09-05' FROM DUAL
),
calendar (day) AS (
SELECT start_date + LEVEL - 1
FROM bounds
CONNECT BY start_date + LEVEL - 1 < end_date
)
SELECT e.empid,
c.day AS accumulation_date,
COALESCE((w.work_end_date_time - w.work_start_date_time) * 24, 0)
AS duty_hours,
COALESCE(
SUM(w.work_end_date_time - w.work_start_date_time) OVER (
PARTITION BY w.workid
ORDER BY c.day DESC
) * 24,
0
) AS accumulated_duty_hours
FROM calendar c
LEFT OUTER JOIN working_hours w
PARTITION BY (w.workid)
ON ( c.day <= w.work_start_date_time
AND w.work_start_date_time < c.day + 1
)
INNER JOIN employee e
ON (w.workid = e.workid)
WHERE e.empid = 1;
Which, for the sample data:
CREATE TABLE employee (empid, workid) AS
SELECT 1, 6 FROM DUAL;
CREATE TABLE working_hours (workid, work_start_date_time, work_end_date_time) AS
SELECT 1, DATE '2024-09-01' + INTERVAL '09:00' HOUR TO MINUTE, DATE '2024-09-01' + INTERVAL '20:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 1, DATE '2024-09-02' + INTERVAL '22:00' HOUR TO MINUTE, DATE '2024-09-03' + INTERVAL '06:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 1, DATE '2024-09-04' + INTERVAL '06:00' HOUR TO MINUTE, DATE '2024-09-04' + INTERVAL '10:00' HOUR TO MINUTE FROM DUAL;
Outputs:
| EMPID | ACCUMULATION_DATE | DUTY_HOURS | ACCUMULATED_DUTY_HOURS |
|---|---|---|---|
| 1 | 2024-09-04 00:00:00 | 4 | 4 |
| 1 | 2024-09-03 00:00:00 | 0 | 4 |
| 1 | 2024-09-02 00:00:00 | 8 | 12 |
| 1 | 2024-09-01 00:00:00 | 11 | 23 |
fiddle
4