CODEWITHSUNDEEP
sqloracle-database
ERKSMTY
ERKSMTY

Reputation: 135

SQL Find Active Cost Based on Order Date

My company uses standard costing, and I'm wondering the best way to find the cost associated with an item at the time it was ordered? I'm using SQL to extract data (not putting data in tables).

For example, I have these costs for item ABC:

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

In a different table, I have these orders for item ABC:

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

Here's how the data should be pulled together, but I'm not sure how:

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

Thanks for your suggestions!

Upvotes: 0

Views: 69

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270723

You can do this with a correlated subquery:

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;

This uses ANSI standard syntax. Databases may differ on how they limit the result to one row.

EDIT:

In earlier versions of Oracle, there are multiple ways to approach this. Here is one method:

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;

Upvotes: 3

Slkrasnodar
Slkrasnodar

Reputation: 824

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;

Upvotes: 0

Related Questions