Query for daily amount outstanding on all active loans

126
March 22, 2018, at 10:52 AM

Suppose i have a single table that contains a history of loans made to clients with the columns:

(ClientName, LoanAmount, LoanStartDate, LoanEndDate)

and the following records:

(Jill Clark, 100.00, 2016-01-01, 2016-01-10)
(James Smith, 200.00, 2016-01-04, 2016-01-07)
(Stewart Little , 10.00, 2016-01-05, 2016-01-06)

I want to write a query that would list (in a sort of time-series format) the total loans outstanding for each calendar day. So for example the final result of the query would be:

Date           Amount Outstanding
2016-01-01     100
2016-01-02     100
2016-01-03     100
2016-01-04     300
2016-01-05     310
2016-01-06     300
2016-01-07     100
2016-01-08     100
2016-01-09     100
2016-01-10     0

An alternative (if the above is not possible would be)

Date           Amount Outstanding
2016-01-01     100
2016-01-04     300
2016-01-05     310
2016-01-06     300
2016-01-07     100
2016-01-10     0

Is this possible? I've searched and haven't found anything similar to this

Answer 1

In MySQL you can use this query to achieve your alternative result (I'm assuming your table is called client_loans):

SELECT Date, IFNULL(SUM(l.LoanAmount), 0) AS `Amount Outstanding`
FROM (SELECT DISTINCT LoanStartDate AS Date FROM client_loans
      UNION SELECT DISTINCT LoanEndDate FROM client_loans ORDER BY Date) AS Dates
LEFT JOIN client_loans l ON l.LoanStartDate <= Date AND l.LoanEndDate > Date 
GROUP BY Date 
Date        Amount Outstanding  
2016-01-01  100
2016-01-04  300
2016-01-05  310
2016-01-06  300
2016-01-07  100
2016-01-10  0
Rent Charter Buses Company
READ ALSO
Spring Cannot add foreign key constraint @ManyToOne

Spring Cannot add foreign key constraint @ManyToOne

I've tried to make this Entity:

167
How come rems are not always computed with the same result

How come rems are not always computed with the same result

Can anyone explain this behavior to me? https://codepenio/anon/pen/BrRpeB

168
Adding comment features to blog using express and handlebars

Adding comment features to blog using express and handlebars

I'm trying to create a blog site that has the ability to comment on a blog/articleI have the end point

224
Uploading PDF Content Into An S3 Bucket

Uploading PDF Content Into An S3 Bucket

I'm trying to download PDF content with data from a remote location and upload the content into S3 as a pdf fileI'm using NodeJS, in the context of an AWS lambda

171