gpt4 book ai didi

sql - SQL计算库存成本

转载 作者:行者123 更新时间:2023-12-01 13:57:53 25 4
gpt4 key购买 nike

我有两张 table 。一个持有特定单位的数量。另一个保存这些单位历史到达时的数量、日期和成本。

所以在我的第一个表中,我会说 UNIT "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 件商品的值(value)。对于 50 个项目,数学看起来像这样:

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

该商品的总值(value)为 $255.00

所以我需要对大约 300,000 件商品执行此操作,并且我需要一个“简易按钮”。目前使用 MS Access 和 SQL 来挖掘我的数据。因此,与这些平台中的任何一个相关的任何解决方案都会很棒。

最佳答案

这个:-

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

关于sql - SQL计算库存成本,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19989399/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com