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
-
05:30
-
04:00
-
00:00
-
10:30
-
9:10
-
07:40
Firebase Cloud Functions: PubSub, "res.on is not a function"
-
04:00
TypeError: Cannot read properties of undefined (reading 'createMessageComponentCollector')
-
9:20
-
7:40
POPULAR ONLINE
- typescript: tsc is not recognized as an internal or external command, operable program or batch file 111570 visits
- search for elements in a list 111065 visits
- In Chrome 55, prevent showing Download button for HTML 5 video 98827 visits
- RxJS5 - error - TypeError: You provided an invalid object where a stream was expected 96332 visits
- Ionic 2 - how to make ion-button with icon and text on two lines? 92528 visits
- NetBeans IDE - ClassNotFoundException: net.ucanaccess.jdbc.UcanaccessDriver 62042 visits
- Adding methods to es6 child class 22376 visits
