如何查找以前日期的值之和

Question

发票表中有3列。

1. InvoicePeriod
2. InvoiceType
费用

我有这样的数据:

InvoicePeriod发票类型费用2020-06-30 ABC 10.02020-06-30 ABC 40.02020-06-30 ABC 32.02020-09-30 ABC 5.02020-09-30 xyz 30.02020-12-31 ABC 20.02020-12-31 ABC 10.02021-01-31 xyz 60.0

现在我想要每种发票类型的最后(最长)发票期限和以前日期的费用总和。

输出:

InvoicePeriod发票类型费用2020-12-31 ABC 87.02021-01-31 xyz 30.0

如何实现?

谢谢,Ankit

Answer 1

您想要group byInvoiceType(因为您想要每个类型一行)，并且您想要聚合函数max和sum来组合这些组中的值。

SELECT MAX(InvoicePeriod), InvoiceType, SUM(Fees)
FROM mytable
GROUP BY InvoiceType

编辑以排除与最大日期匹配的费用，现在我更好地理解了这个问题:

SELECT t2.MaxPeriod, t2.InvoiceType, SUM(CASE WHEN t1.InvoicePeriod < t2.MaxPeriod THEN t1.Fees ELSE 0 END)
FROM test t1 INNER JOIN
(                                              
SELECT MAX(InvoicePeriod) MaxPeriod, InvoiceType
FROM test
GROUP BY InvoiceType
) t2 ON t1.InvoiceType = t2.InvoiceType
GROUP BY t2.MaxPeriod, t2.InvoiceType

有不同的方法可以做到这一点，但我认为上面的方法是你想要的，所以你可以在此基础上进行构建。内部查询获取每个InvoiceType的最大InvoicePeriod。外部查询使用它，并在日期小于该组的最大值时对Fees求和。

Answer 2

我想这就是你要找的。

SELECT
MAX(in_main.InvoicePeriod) AS InvoicePeriod
, InvoiceType
/* Subtract out fees on last invoice date*/
, SUM(Fees) - (
SELECT COALESCE(SUM(Fees), 0)
FROM Invoice in_sub
WHERE (
in_sub.InvoiceType = in_main.InvoiceType
AND
in_sub.InvoicePeriod = MAX(in_main.InvoicePeriod)
)
) AS Fees
FROM Invoice in_main
GROUP BY InvoiceType

http://sqlfiddle.com/!18/288adf/2/0

Answer 3

步骤:

1. 按周期和类型汇总。
2. 获取一个类型之前周期的总和。
3. 将TOP WITH TIES与ROW_NUMBER结合使用，以保留所有类型的最后句号。

查询:

select top(1) with ties
invoiceperiod,
invoicetype,
coalesce(sum(sum(fees)) over (
partition by invoicetype
order by invoiceperiod
rows between unbounded preceding and 1 preceding
), 0.0) as sum_fees
from invoice
group by invoiceperiod, invoicetype
order by row_number() over (partition by invoicetype order by invoiceperiod desc);

演示:https://dbfiddle.uk/?rdbms=sqlserver_2019&小提琴= 6 a651f85961b27be687026c2ce73c8f9