I have a below table
cid | step | cr_time |
---|---|---|
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 |
Now I need to fetch data only if both S02 and S03 time is between 8:30 AM and 4:30PM. I was able to achieve it partially but if any of the step doesnt satisfy the condition the row shouldnt be fetched..In my case it is coming as null. Below is the query
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;
I just need response like below not the null rows
cid | S02_cr_time | S03_cr_time |
---|---|---|
120 | 08-JUL-24 08.35.19.000 AM | 08-JUL-24 01.35.19.000 PM |
Query tester Link : https://sqlize.online/sql/oracle23/22fd2bd79a6de38f592e7c225bde00a5/
Try to add HAVING clause like below:
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
HAVING S02_time IS NOT NULL AND S03_time IS NOT NULL;