Skip to Main Content

eliminating repeating amounts while joining

ElmasduroJul 14 2020 — edited Jul 20 2020

Hi all,

consider the following data

   

WITH charge AS

(

SELECT 8822770 ID, 1001548 DB, 79 AMT FROM DUAL UNION ALL

SELECT 8822771 ID, 1001548 DB, 101 AMT FROM DUAL UNION ALL

SELECT 8822772 ID, 1001548 DB, 173 AMT FROM DUAL

)

,pay AS

(

  SELECT 9065337 ID, 1001548 AS DB, 'ADJ' type, 209 amt from dual union all

  SELECT 9065336 ID, 1001548 AS DB,  'PAY' TYPE, 145 AMT FROM DUAL

)

,alloc AS

(

  SELECT 8822771 CHID, 1001548 CHDB, 9065337  CRID, 1001548  CRDB  , 35 amt FROM DUAL UNION ALL

  SELECT 8822772 CHID, 1001548 CHDB, 9065337  CRID, 1001548  CRDB  , 173 amt FROM DUAL UNION ALL

  SELECT 8822770 CHID, 1001548 CHDB, 9065336  CRID, 1001548  CRBD , 79 amt FROM DUAL UNION ALL

  SELECT 8822771 CHID, 1001548 CHDB, 9065336  CRID, 1001548  CRBD , 66 amt FROM DUAL

)

SELECT  a.id chargeid, a.amt as chargeamt, b.amt allocamt, c.id creditid, c.amt creditamt

   FROM charge a

     JOIN alloc b

      ON (a.id     = b.CHID

         -- AND a.DB = b.CHBD

)

   INNER JOIN pay  c

      ON (b.CRID  = c.id

          AND b.CRDB = c.db

)

the above query is giving me the output below

chargeid      chargeamt      allocamt      creditid      creditamt

8822772        173                 173            9065337     209

8822771       101                 35                9065337   209

8822771        101                66                9065336    145

8822770        79                   79               9065336    145

the charge table contains all my charges, the pay table contains all my payments and the alloc table contains how my payments are allocated to a charge.

the alloc table contains the IDs to be able to link a payment to a specific charge.

the problem with the output above is that when i try to join all tables together to link payment and charges and get a breakdown of the payment (alloc),

my chargeamt can be repeated  and creditamt can also repeat.

for example chargeid=8822771 has 101 twice for chargeamt

creditid= 9065337 , has 209 twice as well as 9065336

therefore, when i try to sum up chargesamt, allocamt and creditamt, chargeamt 101 is getting counted twice as well as creditamt 209, 145

ideally, i want my output to be

chargeid      chargeamt      allocamt      creditid      creditamt

8822772        173                 173            9065337     209

8822771       101                 35                9065337    

8822771                               66                9065336    145

8822770        79                   79               9065336   

as you can see the output above, 101 should not come twice  and 209,145 shouldnt either. but it is coming because of the join which is understandable.

my final output i am looking for is to sum up the output above and get the following

chargeamt            allocamt            payamt            ajdamt

353                        353                   145                    209

i basically took the output above and sum it up.  all charges added up to 353. notice how the charge amount 101 was coming twice and i dont want to counted twice

allocamt sum up to 353,  and payment and adj got separated.  in the pay table 145 is type=pay  and 209 is type=adj

can someone help me modify my query to product the proper output so charges and payments dont get counted twice while summing up charges and payment/adj?

im using oracle 11g

thanks in advance

Comments
Post Details
Added on Jul 14 2020
1 comment
57 views