user3719857 user3719857 - 2 months ago 8
SQL Question

Construct a list of values which are not present in the current set

I have a table which has commodity, supplier and date (some other stuff also). I want to find all unique commodity,suppliers, date who dont have a date in the overall date set. Meaning from my values I find the set of all dates in the table and I want to construct a list of unique commodity,suppliers and date, which do not currently have a date property in the set. For example:

I have the data:

COMMODITY|SUPPLIER|DATE
-----------------------
1 |1 |15.06.16
1 |2 |22.06.16
2 |1 |29.06.16


I want the query to return:

COMMODITY|SUPPLIER|DATE
-----------------------
1 |1 |22.06.16
1 |1 |29.06.16
1 |2 |15.06.16
1 |2 |29.06.16
2 |1 |15.06.16
2 |1 |22.06.16


How can I do this ?

Answer

With Partition Outer Join this is easy to do:

WITH sample_data AS (SELECT 1 commodity, 1 supplier, to_date('15/06/2016', 'dd/mm/yyyy') dt FROM dual UNION ALL
                     SELECT 1 commodity, 2 supplier, to_date('22/06/2016', 'dd/mm/yyyy') dt FROM dual UNION ALL
                     SELECT 2 commodity, 1 supplier, to_date('29/06/2016', 'dd/mm/yyyy') dt FROM dual),
             dts AS (SELECT DISTINCT dt FROM sample_data)
SELECT sd.commodity,
       sd.supplier,
       dts.dt
FROM   dts
       LEFT OUTER JOIN sample_data sd PARTITION BY (sd.commodity, sd.supplier) ON (sd.dt = dts.dt)
WHERE  sd.dt IS NULL;

 COMMODITY   SUPPLIER DT
---------- ---------- -----------
         1          1 22/06/2016
         1          1 29/06/2016
         1          2 15/06/2016
         1          2 29/06/2016
         2          1 15/06/2016
         2          1 22/06/2016

It also means that you only have to reference the table twice - once to get the list of unique dates, and again to join the table to the list of dates you're interested in, so it should be relatively performant.

Comments