SQLize | PHPize | SQLtest

create table t (cid int, step char(3), cr_time timestamp); insert into t values (120, 'S02', '08-JUL-24 09.35.19.000 AM'), (120, 'S03', '08-JUL-24 01.35.19.000 PM'), (120, 'S04', '09-JUL-24 02.35.19.000 PM'), (121, 'S02', '09-JUL-24 07.35.19.000 AM'), (121, 'S03', '09-JUL-24 02.35.19.000 PM'), (122, 'S02', '10-JUL-24 10.35.19.000 AM'), (122, 'S03', '10-JUL-24 05.35.19.000 PM') ; with cte as( select cid, min(case when step = 'S02' then cr_time end) S02_time, min(case when step = 'S03' then cr_time end) S03_time from t where (CAST (cr_time as TIME) >= '8:30:00 AM' and CAST (cr_time as TIME) <= '4:30:00 PM') group by cid ) select * from cte where S02_time IS NOT NULL AND S03_time IS NOT NULL ;