...
By using the following query we can get all active loan ids along with product id, overdue in days, outstanding loan balance, provision category id, provision percentage, due date
| Code Block |
|---|
SELECT loan.id as loanid, |
...
loan.product_id as productid, |
...
GREATEST(datediff(CURDATE(),sch.duedate),0) as numberofdaysoverdue, |
...
loan.total_outstanding_derived as outstandingbalance, |
...
lpc.category_id, |
...
lpc.provision_percentage, |
...
sch.duedate |
...
FROM m_loan_repayment_schedule as sch |
...
LEFT JOIN m_loan as loan |
...
ON sch.loan_id = loan.id |
...
LEFT JOIN m_loanproduct_provision_category lpc |
...
ON loan.product_id=lpc.product_id |
...
and lpc.min_age <= GREATEST(datediff(CURDATE(),sch.duedate),0) |
...
and GREATEST(datediff(CURDATE(),sch.duedate),0) < lpc.max_age |
...
WHERE loan.loan_status_id=300 and sch.principal_amount > 0 and sch.completed_derived=false and |
...
lpc.category_id > 0 and lpc.provision_percentage > 0 |
...
GROUP BY sch.loan_id |
...
HAVING sch.duedate=min(sch.duedate) |
The result of the above query will look like below (mock data).
...