问题描述
我在 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.
- 标记 U - 销售
- Flag I - 进货
- Flag V - 盘点
当标志 U 和 I 出现时，数量代表变化出现Flag V时数量代表盘点时的总量
When Flag U and I appears the amount represent the changeWhen 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 根据不同的标志计算累积总和/条件运行总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！