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
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
Firebase Cloud Functions: PubSub, "res.on is not a function"
TypeError: Cannot read properties of undefined (reading 'createMessageComponentCollector')
Can anyone explain this behavior to me? https://codepenio/anon/pen/BrRpeB
I'm trying to create a blog site that has the ability to comment on a blog/articleI have the end point
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