I have a table in SQL Server with data looking like this example.
我在SQL Server中有一个表,数据看起来像这个例子。
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
I want to calculate ackumulated value based on the transactions. My problem is that the table contains 3 types of transactions.
我想根据交易计算累计值。我的问题是该表包含3种类型的事务。
- Flag U - Sales
- Flag I - Incoming goods
- Flag V - Stocktaking
旗帜U - 销售
旗帜I - 收货
国旗V - Stocktaking
When Flag U and I appears the amount represent the change When Flag V appears the amount represent the total amount when stocktaking
当标志U和I出现时,金额代表变化当标志V出现时,金额代表盘点时的总金额
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.
在单词中我想为每个unice Art.No找到最新的V-transaction,然后加上或减去U和I事务以获得每行的累积和。如果没有V事务,则遍历整个数据集。
I have made examples with expected result for each Art.No
我已经为每个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
Edit: To get more history in the dataset there would be nice to have values before the latest V-transaction like this
编辑:要在数据集中获得更多历史记录,最好在最新的V-transaction之前获得值
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
Where each I and U transaction is taken in consideration but V-transactions is ignored.
考虑每个I和U事务但忽略V事务。
1 个解决方案
#1
3
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
See Fiddle
Edit:
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
Both calculations are mutually exlusive, so you can easily combine them using COALESCE.
两种计算都是相互排斥的,因此您可以使用COALESCE轻松地将它们组合在一起。
See Fiddle
#1
3
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
See Fiddle
Edit:
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
Both calculations are mutually exlusive, so you can easily combine them using COALESCE.
两种计算都是相互排斥的,因此您可以使用COALESCE轻松地将它们组合在一起。
See Fiddle