Oracle SQL - Filter values in a table using date ranges available in another table

Question

I am trying to come up with a Oracle SQL query to list all rows in "External_Order" table in which the order_dt value does not fall within start_dt and end_dt values in orders table for the same cust_id. Is there a way to achieve this using SQL without having to write PL/SQL?

**ORDERS**

CUST_ID  , START_DT,     END_DT
12345     , 01-01-2022,   01-15-2022
12345     , 02-01-2022,   02-28-2022

**EXTERNAL_ORDER**

CUST_ID, ORDER_DT,   AMOUNT
12345,   01-10-2022,  100
12345,   01-16-2022,  200
12345,   01-27-2022,  150
12345,   02-03-2022,  300

**EXPECTED OUTPUT**

CUST_ID  ORDER_ID ,   AMOUNT    
12345,   01-16-2022,  200
12345,   01-27-2022,  150

SOS · Accepted Answer · 2022-03-03 05:55:55Z

2

Perhaps EXISTS might do the trick?

SELECT eo.*
FROM   EXTERNAL_ORDER eo 
WHERE  NOT EXISTS ( 
       SELECT 1
       FROM   ORDERS o 
       WHERE  eo.CUST_ID = o.CUST_ID
       AND    o.START_DT <= eo.ORDER_DT 
       AND    o.END_DT >= eo.ORDER_DT

)

demo db<>fiddle

answered Mar 3, 2022 at 5:55

SOS

6,5602 gold badges14 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Punter Vicky Over a year ago

This works great. Thanks @SOS for your time and effort to help with this.

Collectives™ on Stack Overflow

Oracle SQL - Filter values in a table using date ranges available in another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related