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