Oracle 18c

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(
               TRUNC( date_logged )
               - TRUNC( date_logged, 'IW' ),
               0,  0.0,
               1,  9.5,
               2, 19.0,
               3, 28.5,
               4, 38.0,
                  44.0
             ) / 24
           -- Add the hours of the final day
           + LEAST(
               GREATEST(
                 COALESCE( date_closed, SYSDATE )
                 - ( TRUNC( COALESCE( date_closed, SYSDATE ) )
                     + INTERVAL '07:00' HOUR TO MINUTE
                   ),
                 0
               ),
               DECODE(
                 TRUNC( COALESCE( date_closed, SYSDATE ) )
                 - TRUNC( COALESCE( date_closed, SYSDATE ), 'IW' ),
                 0, 9.5,
                 1, 9.5,
                 2, 9.5,
                 3, 9.5,
                 4, 6.0,
                    0.0
               ) / 24
             )
           -- Subtract the hours of the day before the range starts.
           - LEAST(
               GREATEST(
                 date_logged
                 - ( TRUNC( date_logged ) + INTERVAL '07:00' HOUR TO MINUTE ),
                 0
               ),
               DECODE(
                 TRUNC( date_logged )
                 - TRUNC( date_logged, 'IW' ),
                 0, 9.5,
                 1, 9.5,
                 2, 9.5,
                 3, 9.5,
                 4, 6.0,
                    0.0
               ) / 24
             )
         )
         -- Multiply to give seconds rather than fractions of full days.
         * 24 * 60 * 60
       ) AS service_time_seconds
FROM   table_name
);


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

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