SQL Server 根据不同的标志计算累积总和/条件运行总和

问题描述

我在 SQL Server 中有一个表，其中的数据类似于此示例.

I have a table in SQL Server with data looking like this example.

ID  Flag    Art.No  Amount
1   U   A1000   -100
2   U   B2000   -5
3   V   B2000   900
4   U   B2000   -10
5   I   B2000   50
6   U   B2000   -20
7   U   A1000   -50
8   I   A1000   1000
9   V   A1000   3600
10  U   A1000   -500
11  U   A1000   -100
12  U   A1000   -2000
13  I   A1000   2000
14  U   A1000   -1000
15  I   C3000   10000
16  U   C3000   -4000
17  U   B2000   -5
18  U   B2000   -5
19  I   B2000   40
20  V   B2000   200
21  U   A1000   -500
22  U   B2000   -50
23  U   C3000   -1000

我想根据交易计算累积价值.我的问题是该表包含 3 种类型的交易.

I want to calculate ackumulated value based on the transactions. My problem is that the table contains 3 types of transactions.

1. 标记 U - 销售
2. Flag I - 进货
3. Flag V - 盘点

当标志 U 和 I 出现时，数量代表变化出现Flag V时数量代表盘点时的总量

When Flag U and I appears the amount represent the change When Flag V appears the amount represent the total amount when stocktaking

换句话说，我想找到每个 unice Art.No 的最新 V-transaction，然后添加或减去 U 和 I 交易以获得每行的累计总和.如果没有 V-transaction 则遍历整个数据集.

In words I want to find the latest V-transaction for each unice Art.No and then add or subtract U and I transactions to get a cummulativ sum for each row. If there is no V-transaction go through the whole dataset.

我已经为每个艺术制作了具有预期结果的示例.No

I have made examples with expected result for each Art.No

A1000

ID  Flag    Art.No  Amount  A1000 Example
1   U   A1000   -100    
7   U   A1000   -50 
8   I   A1000   1000    
9   V   A1000   3600    3600
10  U   A1000   -500    3100
11  U   A1000   -100    3000
12  U   A1000   -2000   1000
13  I   A1000   2000    3000
14  U   A1000   -1000   2000
21  U   A1000   -500    1500

B2000

ID  Flag    Art.No  Amount  B2000 Example
2   U   B2000   -5  
3   V   B2000   900 
4   U   B2000   -10 
5   I   B2000   50  
6   U   B2000   -20 
17  U   B2000   -5  
18  U   B2000   -5  
19  I   B2000   40  
20  V   B2000   200 200
22  U   B2000   -50 150

C3000

ID  Flag    Art.No  Amount  C3000 Example
15  I   C3000   10000   10000
16  U   C3000   -4000   6000
23  U   C3000   -1000   5000

为了在数据集中获得更多历史记录，在像这样的最新 V-transaction 之前有值会很好

To get more history in the dataset there would be nice to have values before the latest V-transaction like this

B2000

ID  Flag    Art.No  Amount  B2000 Example
2   U   B2000   -5  150
3   V   B2000   900 140
4   U   B2000   -10 140
5   I   B2000   50  190
6   U   B2000   -20 170
17  U   B2000   -5  165
18  U   B2000   -5  160
19  I   B2000   40  200
20  V   B2000   200 200
22  U   B2000   -50 150

考虑每个 I 和 U 事务但忽略 V 事务.

Where each I and U transaction is taken in consideration but V-transactions is ignored.

推荐答案

with cte as
 (
   select *,
      -- find the latest 'V' ID per ArtNo
      max(case when Flag = 'V' then ID end) 
      over (partition by ArtNo) as Last_V_ID
   from myTable
 )
select *,
   -- cumulative sum, but ignore all rows before the latest 'V' ID
   -- includes rows when there's no 'V' ID for this ArtNo
   sum(case when ID < Last_V_ID then null else Amount end)
   over (partition by ArtNo
         order by ID
         rows unbounded preceding)
from cte
order by ArtNo, ID

参见 Fiddle

要包含上次盘点之前的数据并忽略所有之前的盘点，您可以使用以下方法:

To include the data before the last stocktaking and to ignore all previous stocktakings you can use this approach:

with cte as
 (
   select *,
      -- find the latest 'V' ID per ArtNo
      max(case when Flag = 'V' then ID end) 
      over (partition by ArtNo) as Last_V_ID
   from [dbo].[Warehouse]
 )
select *,
   -- cumulative sum, but ignore all rows before the latest 'V' ID
   -- includes rows when there's no 'V' ID for this ArtNo
   sum(case when ID < Last_V_ID then null else Amount end)
   over (partition by ArtNo
         order by ID
         rows unbounded preceding)
   -- calculate in-stock based on last 'V' ID, discarding all previous 'V' rows
  ,sum(case when (ID < Last_V_ID and Flag <> 'V')  then -Amount 
            when ID = Last_V_ID then Amount 
       end)
   over (partition by ArtNo
         order by ID 
         rows between 1 following and unbounded following)
from cte
order by ArtNo, ID

两种计算都是互斥的，因此您可以使用 COALESCE 轻松地将它们组合起来.

Both calculations are mutually exlusive, so you can easily combine them using COALESCE.

参见 Fiddle

这篇关于SQL Server 根据不同的标志计算累积总和/条件运行总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！