2

Customer (parent table)

Id Charge Amt
C1 10
C2 20
C3 30
C4 40

Payment (child table)

Id pymt amt
C1 10
C2 5
C4 40

Select Id and pending balance from customer and payment tables

Pending balance validations:

  1. If payment amount is >= charge amount then set pending balances to 0
  2. If payment amount is less then set charge amount - payment amount to pending balance.
  3. If payment record not found then set charge amount to pending balances.

My query is not working for third(3) scenario:

I need output like:

Id Pending bal
C1 0
C2 15
C3 30
C4 0

Query: Tried left outer join also but sum of payment is not working.

Select A.ID,
       (SELECT (
        CASE
        WHEN (SUM(B.PYMT AMT) < A.CHARGE AMT)
        THEN (A.CHARGE AMT - SUM(B.PYMT AMT))
        ELSE 0 END)
        FROM PYMT B
        WHERE B.ID = A.ID)
FROM CUST A;
5
  • Please edit your message to show us your query Dec 21, 2022 at 0:13
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Dec 21, 2022 at 0:13
  • @RohitGupta pls check my query i tried. Dec 21, 2022 at 0:25
  • @ErgestBasha , you should make that an answer. Dec 21, 2022 at 10:24
  • Is it possible to have multiple payments for the same customer? Dec 22, 2022 at 6:38

2 Answers 2

1

A simple case without any aggregation function is needed.

select c.Id,
       case when p.pymtamt >= c.ChargeAmt then 0
            when p.pymtamt <  c.ChargeAmt then c.ChargeAmt-p.pymtamt
            when p.pymtamt is null then c.ChargeAmt end as Pendingbal
from Customer c
left join Payment p on c.Id=p.Id;

1.If payment amount is >= charge amount then set pending balances to 0

when p.pymtamt >= c.ChargeAmt then 0

2.If payment amount is less then set charge amount - payment amount to pending balance.

when p.pymtamt <  c.ChargeAmt then c.ChargeAmt-p.pymtamt

3.If payment record not found then set charge amount to pending balances.

when p.pymtamt is null then c.ChargeAmt

pymtamt when doesn't exist will be null because of the left join.

Note, the demo is tested using MySQL , when choosing DB2 it didn't display any records

0

In case of multiple payments for the same customer.

select 
  c.Id
, decode (sign (c.ChargeAmt - coalesce(p.pymtamt, 0)), -1, 0, c.ChargeAmt - coalesce(p.pymtamt, 0))
  as Pendingbal
from Customer c
left join 
(
  select Id, sum (pymtamt) as pymtamt
  from Payment
  group by Id
) p on c.Id=p.Id
order by c.Id
ID PENDINGBAL
C1 0
C2 15
C3 30
C4 0

fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.