Wasky Wasky - 1 month ago 10
SQL Question

Profit (calculated) based on historical Cost price -- Postgres

Having issues calculating profit based on the cost price that was in place at the time of the transaction

Below is a recreation of the scenario

create table price_history(id int,dated date,product_id int,cost_price int);
insert into price_history(id,dated,product_id,cost_price)
values
(6, to_date('2016-10-01','YYYY-MM-DD'),1,200),
(7, to_date('2016-10-02','YYYY-MM-DD'),2,250),
(8, to_date('2016-10-03','YYYY-MM-DD'),1,280),
(9, to_date('2016-10-05','YYYY-MM-DD'),1,300);
commit;

create table sales_trans(id int,dated date,product_id int, sales_price int);
insert into sales_trans(id,dated,product_id,sales_price)
values
(1, to_date('2016-10-01','YYYY-MM-DD'),1,220),
(2, to_date('2016-10-03','YYYY-MM-DD'),1,250),
(3, to_date('2016-10-04','YYYY-MM-DD'),1,300),
(4, to_date('2016-10-06','YYYY-MM-DD'),1,330);


commit;

PRICE_HISTORY

ID | DATE | PRODUCT ID| COST_PRICE
6 | 10/1/2016 | 1 | 200
7 | 10/2/2016 | 2 | 250
8 | 10/3/2016 | 1 | 280
9 | 10/5/2016 | 1 | 300


SALES TRANSACTIONS

ID | DATE | PRODUCT ID| SALES_PRICE
1 | 10/1/2016 | 1 | 220
2 | 10/3/2016 | 1 | 250
3 | 10/4/2016 | 1 | 300
4 | 10/6/2016 | 1 | 330


I would like to have output like below:

DATE | PRODUCT ID| SALES_PRICE | COST_PRICE
10/1/2016 | 1 | 220 | 200
10/3/2016 | 1 | 250 | 280
10/4/2016 | 1 | 300 | 280
10/6/2016 | 1 | 330 | 300

Answer

Use distinct on for joined tables:

select distinct on(t.id, t.dated, t.product_id, sales_price)
    t.id, t.dated, t.product_id, sales_price, 
    cost_price, h.dated as cost_date
from sales_trans t
left join price_history h
on t.product_id = h.product_id and t.dated >= h.dated
order by 1, 2, 3, 4, 6 desc;

 id |   dated    | product_id | sales_price | cost_price | cost_date  
----+------------+------------+-------------+------------+------------
  1 | 2016-10-01 |          1 |         220 |        200 | 2016-10-01
  2 | 2016-10-03 |          1 |         250 |        280 | 2016-10-03
  3 | 2016-10-04 |          1 |         300 |        280 | 2016-10-03
  4 | 2016-10-06 |          1 |         330 |        300 | 2016-10-05
(4 rows)