CODEWITHSUNDEEP
sqloracle-database
user3719857
user3719857

Reputation: 1123

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 ?

Upvotes: 0

Views: 47

Answers (3)

Boneist
Boneist

Reputation: 23588

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.

Upvotes: 1

Mike
Mike

Reputation: 2005

with t as(
 select 1 as COMMODITY, 1 as SUPPLIER, '15.06.16' as "date" from DUAL
 union all
 select 1,2, '22.06.16' from DUAL
 union all
 select 2,1, '29.06.16' from DUAL
)

select *
  from (select distinct commodity, supplier from t),
       (select distinct "date" from t)
MINUS
select * from t

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You can do this by constructing all possible combinations using cross join, then pick out the ones that exist:

select c.commodity, s.supplier, d.date
from (select distinct commodity from t) c cross join
     (select distinct supplier from t) s cross join
     (select distinct date from t) d left join
     t
     on t.commodity = c.commodity and
        t.supplier = s.supplier and
        t.date = s.date
where t.commodity is null;

Upvotes: 1

Related Questions