Reputation: 1123
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
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
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
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