問題描述
我在 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 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 根據不同的標志計算累積總和/條件運行總和的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！