创建一列以根据FIFO计算每次装运的余额部分 [英] Create a column to calculate the portion of balance for every shipment according to FIFO
本文介绍了创建一列以根据FIFO计算每次装运的余额部分的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个查询，以获取带有其余额的库存商品，以及其数量按日期排序的收货交易编码，并且我想创建一个列，该列仅根据先进先出的比例列出每个RS代码的余额原则.作为示例中的附件
I have a query to get Stock-items with it's Balance and Its receiving Transactions cods with its quantity Ordered By date, And i Want to create a column which has only the proportion Balance of every RS Code according to First in First Out Principle. As An Example in the attached sample
，我们有StockItemId =(2222,2262,2263).
we have StockItemId = (2222,2262,2263).
，预期结果将为:与Pic中一样，RS_Portion中每一行的余额取决于代码的数量，并且每个项目的RS_Portion的总和应等于同一项目的GlobalBalance.
and the expected result will be AS: As in Pic the Balance of every row in RS_Portion is depending on the Quantity of the Code and the sum of RS_Portion of every item should equal the GlobalBalance of the same item.
这是我的代码:
WITH M AS (
SELECT WHS.StockItemId,SUM(WHS.OnHandBalance) AS GlobalBalance
FROM Warehouse.WarehouseStocks WHS
GROUP BY WHS.StockItemId
HAVING SUM(WHS.OnHandBalance) > 0
)
SELECT M.StockItemId,M.GlobalBalance,WHWorkOrderHeader.Code,WHWorkOrderDetails.Quantity,WHWorkOrderHeader.Date
FROM M
LEFT OUTER JOIN Warehouse.WHWorkOrderDetails
ON WHWorkOrderDetails.StockItemId = M.StockItemId
LEFT OUTER JOIN Warehouse.WHWorkOrderHeader
ON WHWorkOrderHeader.ID = WHWorkOrderDetails.WHWorkOrderHeaderId
WHERE WHWorkOrderHeader.Type = 'RS' AND M.StockItemId IN (2222,2262,2263)
ORDER BY M.StockItemId ASC, WHWorkOrderHeader.Date DESC
StockItemId,GlobalBalance,Code,Quantity,Date
2222,158.0000,RS-1-1543,1,2017-12-13 07:25:29.727
2222,158.0000,RS-1-1471,77,2017-08-22 14:53:11.880
2222,158.0000,RS-1-1470,77,2017-08-22 14:53:09.920
2222,158.0000,RS-1-1409,5,2017-02-16 13:41:00.740
2222,158.0000,RS-1-1409,5,2017-02-16 13:41:00.740
2222,158.0000,RS-1-1231,150,2015-09-29 15:41:45.000
2222,158.0000,RS-1-1226,100,2015-09-21 09:50:37.000
2262,23.0000,RS-14-371,20,2016-10-16 09:11:57.670
2262,23.0000,RS-14-334,30,2016-08-04 16:16:48.803
2262,23.0000,RS-14-303,18,2016-03-08 13:17:17.023
2262,23.0000,RS-14-301,70,2016-03-01 13:45:49.767
2262,23.0000,RS-14-298,30,2016-02-18 10:10:03.973
2262,23.0000,RS-14-286,2,2016-02-08 10:18:14.203
2262,23.0000,RS-14-285,30,2016-02-07 07:14:01.000
2262,23.0000,RS-14-280,3,2016-02-02 15:11:12.220
2262,23.0000,RS-14-276,1,2016-01-18 12:13:37.860
2262,23.0000,RS-14-274,2,2016-01-14 14:33:53.863
2262,23.0000,RS-14-273,1,2016-01-14 13:25:20.457
2262,23.0000,RS-14-271,1,2016-01-12 16:43:30.397
2262,23.0000,RS-14-270,4,2016-01-12 15:54:43.380
2262,23.0000,RS-14-268,1,2016-01-11 16:43:36.843
2262,23.0000,RS-14-267,1,2016-01-10 13:19:42.617
2262,23.0000,RS-14-266,1,2016-01-06 15:58:00.513
2262,23.0000,RS-14-261,1,2016-01-03 15:20:07.410
2262,23.0000,RS-14-259,6,2015-12-30 13:58:46.217
2262,23.0000,RS-14-258,1,2015-12-30 10:59:23.120
2262,23.0000,RS-14-250,3,2015-12-17 16:32:29.937
2262,23.0000,RS-14-245,1,2015-12-10 14:19:14.910
2262,23.0000,RS-14-240,1,2015-12-06 13:13:45.847
2262,23.0000,RS-14-236,1,2015-11-30 15:36:41.233
2262,23.0000,RS-14-233,4,2015-11-26 12:44:22.067
2262,23.0000,RS-14-228,1,2015-11-23 11:38:35.553
2262,23.0000,RS-14-226,1,2015-11-23 10:11:49.393
2262,23.0000,RS-14-223,2,2015-11-10 13:04:17.540
2263,25.0000,RS-14-301,60,2016-03-01 13:45:49.767
2263,25.0000,RS-14-298,20,2016-02-18 10:10:03.973
2263,25.0000,RS-14-295,1,2016-02-11 17:04:54.423
2263,25.0000,RS-14-294,1,2016-02-10 16:06:13.090
2263,25.0000,RS-14-293,2,2016-02-10 15:58:40.353
2263,25.0000,RS-14-276,1,2016-01-18 12:13:37.860
2263,25.0000,RS-14-274,2,2016-01-14 14:33:53.863
2263,25.0000,RS-14-271,1,2016-01-12 16:43:30.397
2263,25.0000,RS-14-268,1,2016-01-11 16:43:36.843
2263,25.0000,RS-14-267,1,2016-01-10 13:19:42.617
2263,25.0000,RS-14-266,1,2016-01-06 15:58:00.513
2263,25.0000,RS-14-259,6,2015-12-30 13:58:46.217
2263,25.0000,RS-14-258,1,2015-12-30 10:59:23.120
2263,25.0000,RS-14-250,3,2015-12-17 16:32:29.937
2263,25.0000,RS-14-240,1,2015-12-06 13:13:45.847
2263,25.0000,RS-14-236,1,2015-11-30 15:36:41.233
2263,25.0000,RS-14-223,2,2015-11-10 13:04:17.540
推荐答案
这将为您提供您提供的输出:
This will give you the output you have provided:
WITH DataSource AS
(
SELECT *
,[GlobalBalance] - SUM([Quantity]) OVER (PARTITION BY [StockItemId], [GlobalBalance] ORDER BY [Date] DESC) AS [Dif]
,ROW_NUMBER() OVER (PARTITION BY [StockItemId], [GlobalBalance] ORDER BY [Date] DESC) [RecordID]
FROM @DataSource
), DataSourceWithPrevDiff AS
(
SELECT *
,LAG([Dif], 1, NULL) OVER (PARTITION BY [StockItemId], [GlobalBalance] ORDER BY [Date] DESC) AS [PrevDif]
FROM DataSource
)
SELECT [StockItemId]
,[GlobalBalance]
,[Date]
,[Code]
,[Quantity]
,CASE WHEN [Dif] > 0 THEN [Quantity]
WHEN [RecordID] = 1 THEN [Quantity] + [Dif]
WHEN [PrevDif] > 0 THEN [PrevDif]
END AS [RS_Portion]
FROM DataSourceWithPrevDiff
WHERE [Dif] > 0
OR [PrevDif] > 0
OR [RecordID] = 1
ORDER BY [StockItemId]
,[Date] DESC;
当然，您可以将查询拆分为多个部分.将为每个stoc项目计算余额，直到当前行的总和-余额对.另外，我们还需要获取每对的第一项.然后，在最终查询中，我们将显示第一个项目，即到目前为止的总和为正，或者先前的总和为正的项目.
Of course you can split the query on parts. The idea is calculated the sum till the current row for each stoc item - balance pair. Also, we need to get the first item for each pair, too. Then in the final query we are showing the first item, the items where the sum till now is positive, or where the previous sum was positive.
这是完整的工作示例:
DECLARE @DataSource TABLE
(
[StockItemId] INT
,[GlobalBalance] INT
,[Code] VARCHAR(32)
,[Quantity] INT
,[Date] DATETIME2
);
INSERT INTO @DataSource ([StockItemId], [GlobalBalance], [Code], [Quantity], [Date])
VALUES ('2222', '158', 'RS-1-1543', '1', '2017-12-13 07:25:29.727')
,('2222', '158', 'RS-1-1471', '77', '2017-08-22 14:53:11.880')
,('2222', '158', 'RS-1-1470', '77', '2017-08-22 14:53:09.920')
,('2222', '158', 'RS-1-1409', '5', '2017-02-16 13:41:00.740')
,('2222', '158', 'RS-1-1409', '5', '2017-02-16 13:41:00.740')
,('2222', '158', 'RS-1-1231', '150', '2015-09-29 15:41:45.000')
,('2222', '158', 'RS-1-1226', '100', '2015-09-21 09:50:37.000')
,('2262', '23', 'RS-14-371', '20', '2016-10-16 09:11:57.670')
,('2262', '23', 'RS-14-334', '30', '2016-08-04 16:16:48.803')
,('2262', '23', 'RS-14-303', '18', '2016-03-08 13:17:17.023')
,('2262', '23', 'RS-14-301', '70', '2016-03-01 13:45:49.767')
,('2262', '23', 'RS-14-298', '30', '2016-02-18 10:10:03.973')
,('2262', '23', 'RS-14-286', '2', '2016-02-08 10:18:14.203')
,('2262', '23', 'RS-14-285', '30', '2016-02-07 07:14:01.000')
,('2262', '23', 'RS-14-280', '3', '2016-02-02 15:11:12.220')
,('2262', '23', 'RS-14-276', '1', '2016-01-18 12:13:37.860')
,('2262', '23', 'RS-14-274', '2', '2016-01-14 14:33:53.863')
,('2262', '23', 'RS-14-273', '1', '2016-01-14 13:25:20.457')
,('2262', '23', 'RS-14-271', '1', '2016-01-12 16:43:30.397')
,('2262', '23', 'RS-14-270', '4', '2016-01-12 15:54:43.380')
,('2262', '23', 'RS-14-268', '1', '2016-01-11 16:43:36.843')
,('2262', '23', 'RS-14-267', '1', '2016-01-10 13:19:42.617')
,('2262', '23', 'RS-14-266', '1', '2016-01-06 15:58:00.513')
,('2262', '23', 'RS-14-261', '1', '2016-01-03 15:20:07.410')
,('2262', '23', 'RS-14-259', '6', '2015-12-30 13:58:46.217')
,('2262', '23', 'RS-14-258', '1', '2015-12-30 10:59:23.120')
,('2262', '23', 'RS-14-250', '3', '2015-12-17 16:32:29.937')
,('2262', '23', 'RS-14-245', '1', '2015-12-10 14:19:14.910')
,('2262', '23', 'RS-14-240', '1', '2015-12-06 13:13:45.847')
,('2262', '23', 'RS-14-236', '1', '2015-11-30 15:36:41.233')
,('2262', '23', 'RS-14-233', '4', '2015-11-26 12:44:22.067')
,('2262', '23', 'RS-14-228', '1', '2015-11-23 11:38:35.553')
,('2262', '23', 'RS-14-226', '1', '2015-11-23 10:11:49.393')
,('2262', '23', 'RS-14-223', '2', '2015-11-10 13:04:17.540')
,('2263', '25', 'RS-14-301', '60', '2016-03-01 13:45:49.767')
,('2263', '25', 'RS-14-298', '20', '2016-02-18 10:10:03.973')
,('2263', '25', 'RS-14-295', '1', '2016-02-11 17:04:54.423')
,('2263', '25', 'RS-14-294', '1', '2016-02-10 16:06:13.090')
,('2263', '25', 'RS-14-293', '2', '2016-02-10 15:58:40.353')
,('2263', '25', 'RS-14-276', '1', '2016-01-18 12:13:37.860')
,('2263', '25', 'RS-14-274', '2', '2016-01-14 14:33:53.863')
,('2263', '25', 'RS-14-271', '1', '2016-01-12 16:43:30.397')
,('2263', '25', 'RS-14-268', '1', '2016-01-11 16:43:36.843')
,('2263', '25', 'RS-14-267', '1', '2016-01-10 13:19:42.617')
,('2263', '25', 'RS-14-266', '1', '2016-01-06 15:58:00.513')
,('2263', '25', 'RS-14-259', '6', '2015-12-30 13:58:46.217')
,('2263', '25', 'RS-14-258', '1', '2015-12-30 10:59:23.120')
,('2263', '25', 'RS-14-250', '3', '2015-12-17 16:32:29.937')
,('2263', '25', 'RS-14-240', '1', '2015-12-06 13:13:45.847')
,('2263', '25', 'RS-14-236', '1', '2015-11-30 15:36:41.233')
,('2263', '25', 'RS-14-223', '2', '2015-11-10 13:04:17.540');
SELECT *
,SUM([Quantity]) OVER (PARTITION BY [StockItemId], [GlobalBalance] ORDER BY [Date] DESC)
,[GlobalBalance] - SUM([Quantity]) OVER (PARTITION BY [StockItemId], [GlobalBalance] ORDER BY [Date] DESC)
FROM @DataSource
ORDER BY [StockItemId]
,[Date] DESC;
WITH DataSource AS
(
SELECT *
,[GlobalBalance] - SUM([Quantity]) OVER (PARTITION BY [StockItemId], [GlobalBalance] ORDER BY [Date] DESC) AS [Dif]
,ROW_NUMBER() OVER (PARTITION BY [StockItemId], [GlobalBalance] ORDER BY [Date] DESC) [RecordID]
FROM @DataSource
), DataSourceWithPrevDiff AS
(
SELECT *
,LAG([Dif], 1, NULL) OVER (PARTITION BY [StockItemId], [GlobalBalance] ORDER BY [Date] DESC) AS [PrevDif]
FROM DataSource
)
SELECT [StockItemId]
,[GlobalBalance]
,[Date]
,[Code]
,[Quantity]
,CASE WHEN [Dif] > 0 THEN [Quantity]
WHEN [RecordID] = 1 THEN [Quantity] + [Dif]
WHEN [PrevDif] > 0 THEN [PrevDif]
END AS [RS_Portion]
FROM DataSourceWithPrevDiff
WHERE [Dif] > 0
OR [PrevDif] > 0
OR [RecordID] = 1
ORDER BY [StockItemId]
,[Date] DESC;
这篇关于创建一列以根据FIFO计算每次装运的余额部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
