CODEWITHSUNDEEP
sqldatabaseoracle-database
H Varma
H Varma

Reputation: 650

SQL Query - Ignore row if data is null for multiple rows on single condition

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/

Upvotes: 0

Views: 61

Answers (2)

MT0
MT0

Reputation: 168761

You can use HAVING to filter after the aggregation and can subtract the values to find the difference in the times:

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;

Note: Before Oracle 23, you cannot use the aliases defined in the SELECT clause in the HAVING clause.

Which, for the sample data:

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;

Outputs:

CID S02_TIME S03_TIME DIFF
120 2024-07-08 09:35:19.000000 2024-07-08 13:35:19.000000 +000000000 04:00:00.000000

fiddle

Upvotes: 0

Sebastian Srikanth Kumar
Sebastian Srikanth Kumar

Reputation: 59

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;

Upvotes: 2

Related Questions