...
The following query will results all loans which are over due either principal amount or interest or charges.
SELECT loan.product_id as loanid, schloan.loanproduct_id as productid, datediff(CURDATE(),sch.principal_amount, sch.interest_amountduedate) 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()
GROUP BY sch.loan_id HAVING sch.duedate=min(sch.duedate)
| loanid | productid | numberofdaysoverdue | outstandingbalance | duedate |
|---|---|---|---|---|
| 11 | 9 | 859 | 5403.9600 | 2013-05-01 |
| 13 | 11 | 692 | 16300.0000 | 2013-10-15 |
| 27 | 21 | 1418 | 10654.0000 | 2011-10-20 |
| 32 | 26 | 6 | 6600.0000 | 2015-09-01 |
| 39 | 33 | 13 | 7584.7100 | 2015-08-25 |