i using following sql query...
select coalesce(s.open_bal,0) open_balance , coalesce(sum(i.amount),0) gross_fee , coalesce(sum(i.discount),0) discount , coalesce(sum(i.amount) - sum(i.discount),0) net_payable , coalesce(sum(r.reg_fee+r.tut_fee+r.other_fee),0) net_recieved , coalesce(sum(i.amount), 0) - coalesce(sum(i.discount), 0) - coalesce(sum(r.reg_fee+r.tut_fee+r.other_fee), 0) balance_due , b.name batch , b.id batch_id batches
then add if()
negative balance:
coalesce(sum(i.amount) - sum(i.discount), 0) + if(s.open_bal < 0, -s.open_bal, 0) net_payable
edit: moving on fix query.
going down subscribers both invoices , receipts wrong idea itself. you're joining receipts invoices, , if there more 1 of any, other side of join summed twice (n times, actually).
now, i'll give basic idea how query should look. i'm not going coalesce() thing - believe it's unnecessary, sum()
shouldn't return null
s.
select b.name batch_name, b.id batch_id, s.open_bal open_balance, invoices_sum.amount_sum gross_fee, invoices_sum.discount_sum discount, invoices_sum.amount_sum - invoices_sum.discount_sum + if(s.open_bal < 0, -s.open_bal, 0) net_payable, recipts_sum.fee_sum net_recieved fm_batches b left join subscribers s on batch = b.id left join ( select student_id, sum(amount) amount_sum, sum(discount) discount_sum invoices group student_id ) invoices_sum on s.id = invoices_sum.student_id left join ( select student_id, sum(reg_fee + tut_fee + other_fee) fee_sum recipts group student_id ) recipts_sum on s.id = recipts_sum.student_id
you idea.
Comments
Post a Comment