Fetch the difference in result in Oracle

I have two tables table_one and table_two.

table_one has incomplete records while table_two has all records

I want to fetch the difference in each date id_date, however i cant get results in a date where there is no record to join

SELECT   
  ID_DATE, 
  CODE, 
  RATE, 
  SUM(PURCHASES), 
  sum(COMMISSION)
FROM ( 
SELECT 
  A.ID_DATE, 
  A.CODE, 
  A.RATE, 
  (B.PURCHASES - A.PURCHASES) PURCHASES, 
  (B.COMMISSION - A.COMMISSION) COMMISSION
FROM (SELECT * FROM  TABLE_ONE WHERE   ID_DATE BETWEEN  20201101 AND 20201130 ) A LEFT JOIN 
(SELECT  *  FROM TABLE_TWO WHERE   ID_DATE BETWEEN  20201101  AND 20201130 ) B 
ON A.ID_DATE  = B.ID_DATE AND   A.CODE = B.CODE AND A.RATE = B.RATE
)  GROUP BY ID_DATE, CODE, RATE

Link to my fiddle for test click here

I cant get records of id_date 20201111 for example

Answer

Is this what you want?

select t2.id_date, t2.code, t2.rate,
    sum(t2.purchases - coalesce(t1.purchases, 0)) as purchases,
    sum(t2.commission - coalesce(t1.commission, 0)) as commission
from table_two t2
left join table_one t1 on t2.id_date = t1.id_date and t2.code = t1.code and t2.rate = t1.rate
where t2.id_date between 20201101 and 20201130
group by t2.id_date, t2.code, t2.rate

Advertisement

Answer