CODEWITHSUNDEEP
sqloracle-database
Omari Victor Omosa
Omari Victor Omosa

Reputation: 2879

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

Upvotes: 0

Views: 61

Answers (2)

Popeye
Popeye

Reputation: 35930

You need to use proper join and it can be achieved using the following query:

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

db<>fiddle demo

Upvotes: 1

GMB
GMB

Reputation: 222672

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

Upvotes: 1

Related Questions