By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name ( Ticket_Nr, date_logged, date_closed ) AS
SELECT 1234567, DATE '2021-01-06' + INTERVAL '11:30:52' HOUR TO SECOND, NULL FROM DUAL UNION ALL
SELECT 8912345, DATE '2021-01-13' + INTERVAL '09:14:16' HOUR TO SECOND, NULL FROM DUAL UNION ALL
SELECT 6789012, DATE '2021-01-14' + INTERVAL '10:48:28' HOUR TO SECOND, DATE '2021-01-21' + INTERVAL '11:40:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, DATE '2021-01-07' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2021-01-14' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, DATE '2021-01-07' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2021-01-08' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 3, DATE '2021-01-08' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2021-01-09' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 4, DATE '2021-01-09' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2021-01-10' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL
7 rows affected
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS (DY)';
SELECT ticket_nr,
date_logged,
current_datetime,
date_closed,
TO_CHAR( FLOOR( service_time_seconds / 60 / 60 ), 'FM9990' )
|| ':'
|| TO_CHAR( MOD( FLOOR( service_time_seconds / 60 ), 60 ), 'FM00' )
|| ':'
|| TO_CHAR( MOD( service_time_seconds, 60 ), 'FM00' )
AS "SERVICE_TIME HH:MM:SS"
FROM (
SELECT ticket_nr,
date_logged,
SYSDATE AS current_datetime,
date_closed,
ROUND(
(
-- Calculate the full weeks difference from the start of ISO weeks.
(
TRUNC( COALESCE( date_closed, SYSDATE ), 'IW' )
- TRUNC( date_logged, 'IW' )
) * (9.5*4+6)/(7*24)
-- Add the hours for the full days for the final week.
+ DECODE(
TRUNC( COALESCE( date_closed, SYSDATE ) )
- TRUNC( COALESCE( date_closed, SYSDATE ), 'IW' ),
0, 0.0,
1, 9.5,
2, 19.0,
3, 28.5,
4, 38.0,
44.0
) / 24
-- Subtract the hours for the full days from the days of the week
-- before the date logged.
- DECODE(
| TICKET_NR | DATE_LOGGED | CURRENT_DATETIME | DATE_CLOSED | SERVICE_TIME HH:MM:SS |
|---|---|---|---|---|
| 1234567 | 2021-01-06 11:30:52 (WED) | 2021-01-14 12:36:54 (THU) | null | 54:36:02 |
| 8912345 | 2021-01-13 09:14:16 (WED) | 2021-01-14 12:36:54 (THU) | null | 12:52:38 |
| 6789012 | 2021-01-14 10:48:28 (THU) | 2021-01-14 12:36:54 (THU) | 2021-01-21 11:40:00 (THU) | 44:51:32 |
| 1 | 2021-01-07 07:00:00 (THU) | 2021-01-14 12:36:54 (THU) | 2021-01-14 07:00:00 (THU) | 44:00:00 |
| 2 | 2021-01-07 07:00:00 (THU) | 2021-01-14 12:36:54 (THU) | 2021-01-08 07:00:00 (FRI) | 9:30:00 |
| 3 | 2021-01-08 07:00:00 (FRI) | 2021-01-14 12:36:54 (THU) | 2021-01-09 07:00:00 (SAT) | 6:00:00 |
| 4 | 2021-01-09 07:00:00 (SAT) | 2021-01-14 12:36:54 (THU) | 2021-01-10 07:00:00 (SUN) | 0:00:00 |