SQL 根据订单日期查找有效成本

Question

我的公司使用标准成本核算，我想知道在订购时查找与物料相关的成本的最佳方法？我正在使用SQL来提取数据(而不是将数据放在表中(。

例如，我有以下 ABC 项的成本：

Update Date      Cost
12/26/2017       $40
2/1/2017         $43
12/27/2016       $39

在另一个表中，我有 ABC 项的以下订单：

Order Date        Price
1/1/2018          $80
1/1/2017          $84

以下是数据应该如何组合在一起，但我不确定如何：

Order Date        Price     Cost
1/1/2018          $80       $40
1/1/2017          $84       $39

感谢您的建议！

Answer 1

您可以使用相关的子查询来执行此操作：

select o.*,
(select c.cost
from costs c
where c.updatedate <= o.orderdate
order by c.updatedate desc
fetch first 1 row only
) as cost
from orders o;

这使用 ANSI 标准语法。 数据库在如何将结果限制为一行方面可能有所不同。

编辑：

在早期版本的 Oracle 中，有多种方法可以解决这个问题。 这是一种方法：

select o.*,
(select max(c.cost) keep (dense_rank first order by c.updatedate desc)
from costs c
where c.updatedate <= o.orderdate
) as cost
from orders o;

Answer 2

with costs (UpdateDate,Cost) as (
select to_date ('10/27/2017', 'mm/dd/yyyy'),60 from dual union all
select to_date ('11/25/2017', 'mm/dd/yyyy'),50 from dual union all
select to_date ('12/26/2017', 'mm/dd/yyyy'),40 from dual union all
select to_date ('2/1/2017',   'mm/dd/yyyy'),43 from dual union all
select to_date ('11/27/2016', 'mm/dd/yyyy'),39 from dual union all
select to_date ('12/27/2016', 'mm/dd/yyyy'),35 from dual 
)
, orders (OrderDate,Price) as (
select to_date('1/1/2018','mm/dd/yyyy'),80 from dual union all
select to_date('1/1/2017','mm/dd/yyyy'),84 from dual
)
select orderdate, price, 
max(updatedate) updt, 
max(cost) keep(dense_rank first order by updatedate desc) cost
from (
select * from orders join costs on (orders.orderdate >= costs.updatedate)
)
group by orderdate, price;