在SQL中计算库存成本

Question

在SQL中计算库存成本

3

我有两个表格。一个表格记录特定单位的数量。另一个表格记录这些单位历史到达时的数量、日期和成本。

因此，在我的第一个表格中，我会说单位“ABC”的数量为50。

在第二个表格中，我会有以下数据：

Unit   Date arrived   Quantity   Cost
----   ------------   --------   -----
ABC       11/1            100    $3.00
ABC       11/4             15    $5.00
ABC       11/5             25    $6.00

在这个例子中，我需要根据先进先出的系统来计算50件物品的价值。对于这50件物品，数学计算如下：

25 x $6.00
15 x $5.00
10 x $3.00

这个项目的总价值将会是$255.00。

所以我需要对大约300,000个项目进行操作，并且我需要一个“易用按钮”。目前使用MS Access和SQL来挖掘我的数据。因此，任何与这两个平台相关的解决方案都将非常好。

- user2994049

如果你想要根据第二个表格中的数据计算50个"ABC"项目的FIFO成本，你会从第一行得到$3.00。如果你有110个"ABC"项目，那么FIFO单位成本将为$350/110。这符合你的需求吗？你要求的是"先进先出"，但你只有"进"的数据。 - WarrenT

我想我已经理解了这个上下文中FIFO的含义。实际上，库存是按日期顺序加载（FI），但假设货架上剩余的任何物品只是因为最早的交付中提取的任何物品都已被提取（FO）。因此，持有任何库存的价值将从最新交付的最新价格开始计算，然后向后计算，直到库存的价值被计算出来。很明显，我已经很久没有编写过库存控制系统了... - dav1dsm1th

1

@dav1dsm1th 是的，那样更有意义，并符合发布的细节。我想这表明我已经很久没有处理库存成本了，哈哈 - WarrenT

您是否正在计算当前值，随着库存水平的变化？如果是这样，物品是否按批次跟踪？如果是YES和NO，则可以假设最先进入的将首先被移除。如果是YES和YES，则您的方法变得更加复杂。您的帖子没有提出这些问题，但它们对于库存估值来说是很典型的。 - Smandoli

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- dav1dsm1th · Accepted Answer

This:-

select s.unit, dv.cost_2d+((s.quantity-dv.quantity_2d)*dv.cost) as valuation
from (
    select 
        d.*, 
        isnull((
            select sum(csq.quantity) 
            from #delivery csq 
            where csq.unit=d.unit 
                and csq.arrived>d.arrived
        ),0) as quantity_2d,
        isnull((
            select sum(csq.quantity*csq.cost) 
            from #delivery csq 
            where csq.unit=d.unit 
                and csq.arrived>d.arrived
        ),0) as cost_2d
    from #delivery d

    -- possible optimization - reduces the number of rows
    -- if we have enough to calculate value of stock held

    --join #stock s on s.unit=d.unit
    --and isnull((
    --    select sum(csq.quantity)
    --    from #delivery csq
    --    where csq.unit=d.unit 
    --        and csq.arrived>d.arrived
    --    ),0)<=s.quantity 

    -- you'd need to test if it helps/hinders with your dataset/schema

) as dv
join #stock s on s.unit=dv.unit
where dv.quantity+dv.quantity_2d>=s.quantity 
    and dv.quantity_2d<s.quantity

产生：

unit    valuation
abc     255.00

如果源自：-

create table #stock (
    unit varchar(10),
    quantity int
)

create table #delivery (
    unit varchar(10),
    arrived date,
    quantity int,
    cost money
)

insert into #stock values ('abc',50)
insert into #delivery values ('abc','2013-11-01',100,3)
insert into #delivery values ('abc','2013-11-04',15,5)
insert into #delivery values ('abc','2013-11-05',25,6)

-----------更新-----------------------------------

这里有另一个版本，可能会更快地运行 - 取决于您的数据集/架构：

select dv.unit, dv.cost_2d+((dv.instock-dv.quantity_2d)*dv.cost) as valuation
from (
    select 
        d.*, 
        isnull((
            select sum(csq.quantity) 
            from #delivery csq 
            where csq.unit=d.unit 
                and csq.arrived>d.arrived
        ),0) as quantity_2d,
        isnull((
            select sum(csq.quantity*csq.cost) 
            from #delivery csq 
            where csq.unit=d.unit 
                and csq.arrived>d.arrived
        ),0) as cost_2d,
        s.quantity as instock
    from #delivery d
    join #stock s on s.unit=d.unit
    and s.quantity between isnull((
            select sum(csq.quantity) 
            from #delivery csq 
            where csq.unit=d.unit 
                and csq.arrived>d.arrived
        ),0) and isnull((
            select sum(csq.quantity) 
            from #delivery csq 
            where csq.unit=d.unit 
                and csq.arrived>d.arrived
        ),0) + d.quantity
) as dv