使用SQL在库存中实现FIFO(FIFO Implementation in Inventory using SQL)

使用SQL在库存中实现FIFO(FIFO Implementation in Inventory using SQL)

这基本上是一个库存项目，分别通过采购和销售跟踪项目的“库存进货”和“库存”。

库存系统遵循FIFO方法（首先购买的物品总是首先销售）。 例如：

如果我们在一月，二月和三月的时间内购买了物品A当客户来的时候，我们放弃了在一月份购买的物品，仅当一月物品结束时，我们开始赠送二月物品等等

所以我必须在这里展示我手中的总库存量和分解量，这样我才能看到总成本。

实际表格数据：

我需要获得的结果集：

我的客户坚持我不应该使用光标，那么还有其他方式吗？

This is basically an inventory project which tracks the "Stock In" and "Stock Out" of items through Purchase and sales respectively.

The inventory system follows FIFO Method (the items which are first purchased are always sold first). For example:

If we purchased Item A in months January, February and March When a customer comes we give away items purchased during January only when the January items are over we starts giving away February items and so on

So I have to show here the total stock in my hand and the split up so that I can see the total cost incurred.

Actual table data:

The result set I need to obtain:

My client insists that I should not use Cursor, so is there any other way of doing so?

最满意答案

正如一些评论已经说过，CTE可以解决这个问题

with cte as ( select item, wh, stock_in, stock_out, price, value , row_number() over (partition by item, wh order by item, wh) as rank from myTable) select a.item, a.wh , a.stock_in - coalesce(b.stock_out, 0) stock , a.price , a.value - coalesce(b.value, 0) value from cte a left join cte b on a.item = b.item and a.wh = b.wh and a.rank = b.rank - 1 where a.stock_in - coalesce(b.stock_out, 0) > 0

如果第二个“项目B”的价格错误（I价格为25，OUT为5）。 SQL 2008小提琴

只是为了好玩，在SQL Server 2012和引入LEAD和LAG功能的同时，可能会以更简单的方式实现

with cte as ( select item, wh, stock_in , coalesce(LEAD(stock_out) OVER (partition by item, wh order by item, wh), 0) stock_out , price, value , coalesce(LEAD(value) OVER (partition by item, wh order by item, wh), 0) value_out from myTable) select item , wh , (stock_in - stock_out) stock , price , (value - value_out) value from cte where (stock_in - stock_out) > 0

SQL2012小提琴

更新 注意 - >要在这一点之前使用这两个查询，数据需要按照正确的顺序。

要获得每天多于一行的详细信息，您需要一些可靠的命令来排列具有相同日期的行，例如具有时间的日期列，自动增量标识或同一行中的某个行，并且已无法使用该查询因为它们是基于数据的位置而编写的。

一个更好的想法是将I和OUT中的数据拆分，按项目，wh和数据排序，并在两个数据上应用排名，如下所示：

SELECT d_in.item , d_in.wh , d_in.stock_in - coalesce(d_out.stock_out, 0) stock , d_in.price , d_in.value - coalesce(d_out.value, 0) value FROM (SELECT item, wh, stock_in, price, value , rank = row_number() OVER (PARTITIO BY item, wh ORDER BY item, wh, date) FROM myTable WHERE stock_out = 0) d_in LEFT JOI (SELECT item, wh, stock_out, price, value , rank = row_number() OVER (PARTITIO BY item, wh ORDER BY item, wh, date) FROM myTable WHERE stock_in = 0) d_out O d_in.item = d_out.item AD d_in.wh = d_out.wh AD d_in.rank = d_out.rank WHERE d_in.stock_in - coalesce(d_out.stock_out, 0) > 0

SQLFiddle

但是这个查询不是完全可靠的，同一个订单组中的数据顺序并不稳定。

如果I.price与OUT.price不同，我还没有更改查询来重新计算价格

As some comment already said a CTE can solve this

with cte as ( select item, wh, stock_in, stock_out, price, value , row_number() over (partition by item, wh order by item, wh) as rank from myTable) select a.item, a.wh , a.stock_in - coalesce(b.stock_out, 0) stock , a.price , a.value - coalesce(b.value, 0) value from cte a left join cte b on a.item = b.item and a.wh = b.wh and a.rank = b.rank - 1 where a.stock_in - coalesce(b.stock_out, 0) > 0

If the second "Item B" has the wrong price (the I price is 25, the OUT is 5). SQL 2008 fiddle

Just for fun, with sql server 2012 and the introduction of the LEAD and LAG function the same thing is possible in a somewhat easier way

with cte as ( select item, wh, stock_in , coalesce(LEAD(stock_out) OVER (partition by item, wh order by item, wh), 0) stock_out , price, value , coalesce(LEAD(value) OVER (partition by item, wh order by item, wh), 0) value_out from myTable) select item , wh , (stock_in - stock_out) stock , price , (value - value_out) value from cte where (stock_in - stock_out) > 0

SQL2012 fiddle

Update ATTETIO -> To use the two query before this point the data need to be in the correct order.

To have the details with more then one row per day you need something reliable to order the row with the same date, like a date column with time, an autoincremental ID or something down the same line, and it's not possible to use the query already written because they are based on the position of the data.

A better idea is to split the data in I and OUT, order it by item, wh and data, and apply a rank on both data, like this:

SELECT d_in.item , d_in.wh , d_in.stock_in - coalesce(d_out.stock_out, 0) stock , d_in.price , d_in.value - coalesce(d_out.value, 0) value FROM (SELECT item, wh, stock_in, price, value , rank = row_number() OVER (PARTITIO BY item, wh ORDER BY item, wh, date) FROM myTable WHERE stock_out = 0) d_in LEFT JOI (SELECT item, wh, stock_out, price, value , rank = row_number() OVER (PARTITIO BY item, wh ORDER BY item, wh, date) FROM myTable WHERE stock_in = 0) d_out O d_in.item = d_out.item AD d_in.wh = d_out.wh AD d_in.rank = d_out.rank WHERE d_in.stock_in - coalesce(d_out.stock_out, 0) > 0

SQLFiddle

But this query is OT completely reliable, the order of data in the same order group is not stable.

I haven't change the query to recalculate the price if the I.price is different from the OUT.price