使用SQL在清单中实现FIFO [英] FIFO Implementation in Inventory using SQL

问题描述

这基本上是一个库存项目，可以通过购买和销售分别跟踪物料的入库"和出库".

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

库存系统遵循FIFO方法(始终先购买的是先购买的物品).例如:

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

如果我们在1月，2月和3月的几个月内购买了商品A 当客户到来时，我们会赠送一月份购买的物品 只有当1月的商品结束时，我们才开始赠送2月的商品，依此类推

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.

实际表格数据:

我需要获得的结果集:

我的客户坚称我不应该使用游标，那么还有其他方法可以使用吗?

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

推荐答案

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

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

如果第二个项目B"的价格错误(IN价格为25，OUT价格为35).
SQL 2008小提琴

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

只是为了好玩，使用sql server 2012以及引入LEAD和LAG函数，可以用更简单的方式实现相同的事情

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小提琴

更新
注意->要在此之前使用两个查询，数据需要以正确的顺序排列.

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

要获得每天多于一行的详细信息，您需要可靠的东西来对具有相同日期的行进行排序，例如带时间的日期列，自动增量ID或同一行中的某项，并且无法使用该查询已写入，因为它们基于数据的位置.

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.

一个更好的主意是将数据分为IN和OUT，按项目，wh和数据排序，并对这两个数据应用排名，如下所示:

A better idea is to split the data in IN 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 
               (PARTITION BY item, wh ORDER BY item, wh, date)
        FROM   myTable
        WHERE  stock_out = 0) d_in
       LEFT JOIN
       (SELECT item, wh, stock_out, price, value
             , rank = row_number() OVER 
               (PARTITION BY item, wh ORDER BY item, wh, date)
        FROM   myTable
        WHERE  stock_in = 0) d_out
       ON d_in.item = d_out.item AND d_in.wh = d_out.wh 
      AND d_in.rank = d_out.rank
WHERE d_in.stock_in - coalesce(d_out.stock_out, 0) > 0

SQLFiddle

但是此查询不是完全可靠的，同一顺序组中的数据顺序不稳定.

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

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

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

这篇关于使用SQL在清单中实现FIFO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！