...

Provision Calculation For Loan:

The By using the following query will results all loans which are over due either principal amount or interest or charges.we can get all active loan ids along with product id, number of days overdue, outstanding loan balance, due date

SELECT loan.id as loanid,
loan.product_id as productid,
GREATEST(datediff(CURDATE(),sch.duedate),0) as numberofdaysoverdue,
loan.total_outstanding_derived as outstandingbalance,
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()   0     
GROUP BY sch.loan_id HAVING sch.duedate=min(sch.duedate)

The result of the above query will look like below (mock data).