sql - 根据日期条件计算多付金额 - SQL Server

Question

我在 SQL 中有一个代码来检索在终止日期之后支付日期的会员，现在我还需要计算在终止日期之后支付的总金额。我怎样才能做到这一点？

有2个表，成员表：

我想要的最终结果是：

目前我能够检索其他列，但 Sum(Rcvd Amount)

Select Member_ID
       , Term_DT
       , RCVD.MAX_RCVD_DT
From Membership a
Left Join
     (
      Select Member_ID
           , MAX(RCVD_DT) as MAX_RCVD_DT
      from received
      group by Member_ID
      )
 AS RCVD on RCVD.Member_ID = a.Member_ID

 WHERE RCVD.MAX_RCVD_DT >= Term_DT

标签： sqlsql-serverdatabase

Answer 1

像这样的东西

select m.Member_ID [Member ID], m.Term_DT [Term Date], 
       sum(r.RCVD_AMOUNT) [Rcvd Amount], max(r.RCVD_DT) [Latest Rcvd Date]
from membership m
     join received r on m.Member_ID = r.Member_ID
                        and r.RCVD_DT >= m.Term_DT
group by m.Member_ID, m.Term_DT;