...

Provision Calculation For Loan:

The following query will results all loans which are over due either principal amount or interest or charges.

SELECT loan.product_id, sch.loan_id, sch.principal_amount, sch.interest_amount, sch.duedate
FROM m_loan_repayment_schedule as sch
LEFT JOIN m_loan as loan
ON sch.loan_id = loan.id
WHERE loan.loan_status_id=300 and sch.principal_amount > 0 AND sch.completed_derived=false and sch.duedate < CURDATE()    
GROUP BY sch.loan_id HAVING sch.duedate=min(sch.duedate)