如何在聚合函数中添加最早交易的原始金额

Question

我正在尝试找出如何将第一笔交易的原始金额(发布日期最早(包含在聚合查询中。

以下查找反向交易..

SELECT DISTINCT   
    [Account], [Voucher],
    [DocumentDate],
    SUM([Amount])   
FROM            
    MyTable 
WHERE           
    [Account] = 'abc'
GROUP BY
    [Account], [Voucher], [DocumentDate]
HAVING          
    SUM([Amount]) = 0

如何在每条记录的最早过账日期的交易结果中包含金额？

例如，使用以下方法：

Account   Voucher   DocumentDate   PostedDate   Amount
---------------------------------------------------------
abc         1       01/01/2018     08/01/2018    100.00
abc         1       01/01/2018     15/01/2018   -100.00 

预期结果将是：

Account   Voucher   DocumentDate   OriginalAmount   Sum(Amount)   Records
-------------------------------------------------------------------------
abc          1      01/01/2018        100.00           0.00          2

Answer 1

一种方法是使用带有 first_value 、 sum...over 和 count...over 的 cte。

首先，创建并填充示例表(请在以后的问题中保存此步骤(

DECLARE @T AS TABLE
(
    Account char(3),
    Voucher int,
    DocumentDate date,
    PostedDate date,
    Amount numeric(5,2)
)
INSERT INTO @T VALUES
('abc', 1, '2018-01-01', '2018-01-08', 100),
('abc', 1, '2018-01-01', '2018-01-15', -100)

反恐委员会：

;WITH CTE
AS
(
    SELECT  [Account], 
            [Voucher],
            [DocumentDate],
            FIRST_VALUE(Amount) OVER(PARTITION BY [Account], [Voucher], [DocumentDate] ORDER BY PostedDate) AS OriginalAmount,
            SUM([Amount]) OVER(PARTITION BY [Account], [Voucher], [DocumentDate]) AS [Sum(Amount)],
            COUNT(*) OVER(PARTITION BY [Account], [Voucher], [DocumentDate]) Records
    FROM            
        @T 
    WHERE           
        [Account] = 'abc'
)

查询：

SELECT DISTINCT *
FROM CTE 
WHERE [Sum(Amount)] = 0

结果：

Account     Voucher     DocumentDate            OriginalAmount  Sum(Amount)     Records
abc         1           01.01.2018 00:00:00     100,00          0,00            2

在 rextester 上观看现场演示。

Answer 2

这似乎很简单...我错过了什么吗？

WITH CTE AS 
(
    SELECT 
        [Account], 
        [Voucher],
        [DocumentDate],
        ROW_NUMBER() OVER (PARTITION BY [Account],[Voucher] ORDER BY [DocumentDate]) RN,
        [Amount]
    FROM            
        MyTable 
    WHERE           
        [Account] = 'abc'
)
SELECT 
        [Account], 
        [Voucher],
        [DocumentDate],
        max(case when RN = 1 THEN [Amount] else null end) OriginalAmount,
        sum([Amount]) SUM_Amount,
        count(*) Records
from cte
GROUP BY
    [Account], [Voucher], [DocumentDate]
HAVING          
    SUM([Amount]) = 0