0

Tables:

cust table:    
   cust_id, name, etc    

bill table:    
   bill_id, cust_id, amt, due_date, status (open/closed)

payment table:    
   payment_id, bill_id, amt etc

Customer can settle a single bill by paying multiple installments. So, one bill_id may relate to payment_ids.

I am unable to generate this recordset:

cust_id | due amt

'due amt' is the sum of all bill.amts - sum of all payment.amts and having status open.

Bill table

bill_id cust_id     amt     status
1       18          200     open
2       18          200     open
3       17          200     open
4       17          200     open
5       17          200     open
6       17          200     closed

Payment table

payment_id  bill_id cust_id amt
1           1       18      50
2           2       18      40
3           3       17      10

Expected output

cust_id     due_amt         hint/how
17          590             (600-10) .. 600 -> because one is closed
18          310             (400-(50+40))
HumbleLearner
  • 103
  • 2
  • 9

1 Answers1

2
select c.cust_id, sum(b.amt) - sum(p.amt) as due_amt
from cust c
left join bill b on b.cust_id = c.cust_id and b.status = 'open'
left join payment p on p.bill_id = b.bill_id
group by c.cust_id

SQLFiddle demo

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • I tested your solution, but it misses out to sum "open" bills for which payments are yet to be received. – HumbleLearner Aug 17 '13 at 17:07
  • Can you please add example data and expected output to your question? – juergen d Aug 17 '13 at 17:12
  • I added data and expected output. When ran in mysql, your solution was giving 190, 310 instead of 590 and 330. – HumbleLearner Aug 17 '13 at 17:33
  • Sorry made a typo: its 190, 310 and 590, 310. Because of join, it didn't take into account the bills which didn't have payments while summing. – HumbleLearner Aug 17 '13 at 17:48
  • I should have read this without bugging you. http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join – HumbleLearner Aug 17 '13 at 17:58