Search code examples
sqloracleoracle-sqldeveloper

Oracle query to count exactly same match with array of dates

There is an Oracle database with the following two tables. The connection is 1:n, so one business can have many orders.

enter image description here

Now I have to write an SQL statement, which checks, whether a business under a given name exists, where a predefined Order dates are there.

Example: We have business with name "B1", and this business has two 'Orders' with "7/4/2020" and "3/1/2021". Let's say, I have a name "B1" and an input set ('7/4/2020', '8/1/2021') which should return something that signalizes that "B1 with exactly these order dates not exist". But with name "B1" and input set ('3/1/2021', '7/4/2020') returns "B1 with exactly these order dates already exist".

My problem: I don't find the right syntax for that. How can I use dates in input set for "IN" operator? And what would be the most performant idea to do that?

Solution

  • If you are always comparing two order dates then try this:

    Schema and insert statements:

     create table business_data (id int ,name varchar(50));
 CREATE TABLE ORDERS (ID INT, ORDERDATE DATE, BUSINESS_ID INT);

 INSERT INTO BUSINESS_DATA VALUES (1,'B1');

 INSERT INTO orders VALUES (1,'3-jan-2021',1);
 INSERT INTO orders VALUES (2,'7-apr-2020',1);
 INSERT INTO orders VALUES (3,'7-may-2020',1);

    Query#1 (with matching order dates)

     select (case when count(*)>0 then (max(name)||' with exactly these order dates already exist') else (max(name)||' with exactly these order dates not exist')  end)as results from business_data b 
 where b.name='B1' and exists 
            (
               select 1  from orders o where b.id=o.business_id and o.orderdate = any ('3-jan-2021', '7-apr-2020') 
            group by business_id
            having count(distinct orderdate)>=2
            )
    RESULTS
    B1 with exactly these order dates already exist

    Query#2 (with order unmatched order date)

     select (case when count(*)>0 then (max(name)||' with exactly these order dates already exist') else (max(name)||' with exactly these order dates not exist')  end)as results from business_data b 
 where b.name='B1' and exists 
            (
               select 1  from orders o where b.id=o.business_id and o.orderdate = any ('3-jan-2021', '8-apr-2120') 
            group by business_id
            having count(distinct orderdate)>=2
            )

    Outupt:

    RESULTS
    with exactly these order dates not exist

    db<>fiddle here