MySQL创建查询以根据两个表的日期计算 future 余额

Question

这可能是一个简单的问题，但我无法弄清楚。

我有两个表中的数据 - 1)贷款 2)还款。我被要求制作一份报告，根据我们当前的投资组合计算每个季度的预期余额。

所以我采取的方法是取贷款表中所有余额的总和(这给出了我们当前的余额)和还款表中所有预期付款的总和，并按季度(到期日)和年份(到期日)分组)减去，以便我们可以预测当时贷款的偿还金额。

我复制下面的代码:

select sum(la.PRINCIPALBALANCE) - due.due as balance 
    ,due.q
    ,due.y
from loans la
join (select 
        PARENTACCOUNTKEY
        ,sum(principaldue) as due
        ,quarter(duedate) as q
        ,year(duedate) as y
    from repayment 
    group by quarter(duedate),year(duedate)) as due 
on la.encodedkey = due.parentaccountkey
group by due.q ,due.y

我希望看到的是如果我们有以下简化数据:

贷款:

ID        principalbalance  
1         2500  
2         2500  

还款:

Parentaccountkey    principaldue      duedate  
1                   500               Q1  
1                   500               Q2   
1                   500               Q3  
2                   100               Q1  
2                   100               Q2  
2                   100               Q2  

我希望我的结果表显示以下内容

Q1     4400 (which is the 2500 + 2500 principalbalance starting point, minus the 500 + 100 due in that month.
Q2     3800 (the 2500 + 2500, minus the 500 + 100 for Q1 and minus 500 + 100 for Q2)
Q3     3200 (the 2500 + 2500 minus the amounts for Q1, Q2 & Q3)

Answer 1

编写一个子查询来计算每个季度本金的运行总计。然后将其与计算总余额的查询连接起来，以便它可以为每个季度减去它。

SELECT duedate, total - running_due AS balance
FROM (SELECT a.duedate, SUM(b.principaldue) AS running_due
      FROM (SELECT DISTINCT duedate
            FROM repayments) AS a
      JOIN repayments AS b ON b.duedate <= a.duedate
      GROUP BY a.duedate) AS x
CROSS JOIN (SELECT SUM(principalbalance) AS total
            FROM loans) AS y
ORDER BY duedate

还有其他方法可以计算运行总计，您可以搜索它们并将子查询替换为最适合您数据库的子查询。