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

2021-09-10 00:00:00 sql tsql 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

相关文章