sql-server - 如何在同一查询中返回多个日期的值

Question

我有两张桌子：

@支付

@ControlNo

每个日期代表TotalIncurred截至该日期的金额。

因此，如果我想获得最后一个（截至今天），我将简单地使用 self join withMAX(PaymentID)

INNER JOIN 
    (SELECT ClaimID, MAX(PaymentID) AS MaxPaymentID 
     FROM @Payments mp 
     GROUP BY ClaimID) a ON a.ClaimID = p.ClaimID AND a.MaxPaymentID = p.PaymentID

但是我怎么会也带来 TotalIncurredas of 2019-05-22，让我们说 as of '2019-05-08'。我不需要为所有日期调整它，只需要上周、上个月、上个季度等。

结果应如下右图所示：

在同一个查询中可以做到这一点吗？

代码示例：

DECLARE @Payments TABLE 
                  (  
                      ClaimID INT, 
                      PaymentID INT,  
                      TotalIncurred MONEY, 
                      dateCreated DATE
                  )

INSERT INTO @Payments 
VALUES (123, 1131215, 250, '2019-03-01'),
       (123, 1132307, 130, '2019-05-18'),
       (123, 1140297, 50, '2019-05-22'),
       (123, 1155063, 80, '2019-06-30')

DECLARE @ControlNo TABLE (ClaimID int, ControlNo int)

INSERT INTO @ControlNo 
VALUES (123, 54321)

--SELECT * FROM @Payments
--SELECT * FROM @ControlNo

SELECT 
    c.ControlNo,
    SUM(TotalIncurred) AS TotalIncurred
FROM 
    @Payments p 
INNER JOIN 
    (SELECT ClaimID, MAX(PaymentID) AS MaxPaymentID 
     FROM @Payments mp 
     GROUP BY ClaimID) a ON a.ClaimID = p.ClaimID AND a.MaxPaymentID = p.PaymentID
INNER JOIN 
    @ControlNo c ON c.ClaimID = p.ClaimID
GROUP BY 
    c.ControlNo

-- For the last week I'd use DateCreated <= GETDATE() -7
SELECT 
    c.ControlNo,
    SUM(TotalIncurred) AS TotalIncurred
FROM 
    @Payments p 
INNER JOIN 
    (SELECT ClaimID, MAX(PaymentID) AS MaxPaymentID 
     FROM @Payments mp 
     WHERE DateCreated < = GETDATE() - 7  
     GROUP BY ClaimID) a ON a.ClaimID = p.ClaimID AND a.MaxPaymentID = p.PaymentID
INNER JOIN 
    @ControlNo c ON c.ClaimID = p.ClaimID
GROUP BY 
    c.ControlNo

标签： sql-servertsqlsql-server-2012