user3719857 user3719857 - 1 year ago 58
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download