问题描述
我在 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