I am kind of circling with this issue.I have a scenario where leases have a number of scheduled payments called “installments”. The total number of installments on a lease is referred to as the “term” of the lease. Installments are typically scheduled on a monthly basis. Hence the unit of a lease term is in months. Smart Pay has leases on 11, 13, 15, 24 month terms.
Imagine a small database with the following tables, Below format isn't formatted. I have attached in Table format of data in Word file as attachment
id funding_date status amount term
523433 2018-01-01 active 700 13
243434 2017-06-15 delinquent 450 13
434344 2017-09-13 closed 500 13
id lease_id amount due_date status
24443 523433 53.85 2018-01-01 paid
43434 523433 53.85 2018-02-01 paid
65653 523433 53.85 2018-03-01 pending
- What query will give the second installment of every lease?
- What query will calculate the amount outstanding of every “active” lease?
Amount Outstanding = Lease Amount - Paid Installment Amounts
- What query will find all leases that are “delinquent” with a total number of installments that is less than the leases’ term?
Appreciate your inputs..many thanks