SQLize | PHPize | SQLtest

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'; CREATE TABLE t ( cid INT, step CHAR(3), cr_time TIMESTAMP ); INSERT INTO t (cid, step, cr_time) SELECT 120, 'S02', TIMESTAMP '2024-07-08 09:35:19.000' FROM DUAL UNION ALL SELECT 120, 'S03', TIMESTAMP '2024-07-08 13:35:19.000' FROM DUAL UNION ALL SELECT 120, 'S04', TIMESTAMP '2024-07-09 14:35:19.000' FROM DUAL UNION ALL SELECT 121, 'S02', TIMESTAMP '2024-07-09 07:35:19.000' FROM DUAL UNION ALL SELECT 121, 'S03', TIMESTAMP '2024-07-09 14:35:19.000' FROM DUAL UNION ALL SELECT 122, 'S02', TIMESTAMP '2024-07-10 10:35:19.000' FROM DUAL UNION ALL SELECT 122, 'S03', TIMESTAMP '2024-07-10 17:35:19.000' FROM DUAL; SELECT cid, MIN(CASE WHEN step = 'S02' THEN cr_time END) S02_time, MIN(CASE WHEN step = 'S03' THEN cr_time END) S03_time, MIN(CASE WHEN step = 'S03' THEN cr_time END) - MIN(CASE WHEN step = 'S02' THEN cr_time END) AS diff FROM t WHERE CAST(cr_time AS TIME) BETWEEN TIME '08:30:00' AND TIME '16:30:00' GROUP BY cid HAVING MIN(CASE WHEN step = 'S02' THEN cr_time END) IS NOT NULL AND MIN(CASE WHEN step = 'S03' THEN cr_time END) IS NOT NULL;