Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table prd (
  start_date date,
  product varchar(20),
  price int
);

insert into prd values
('2023-04-01',	'prod_A',	10),
('2023-04-15',	'prod_A',	20),
('2023-04-01',	'prod_B',	20);

create table ord (
  order_date date,
  id int,
  product varchar(20)
);

insert into ord values
('2023-04-01',	10001,	'prod_A'),
('2023-04-01',	10001,	'prod_B'),
('2023-04-02',	10002,	'prod_A'),
('2023-04-02',	10002,	'prod_B'),
('2023-04-16',	10003,	'prod_A'),
('2023-04-16',	10003,	'prod_B');

CREATE TABLE



INSERT 0 3



CREATE TABLE



INSERT 0 6


select o.*, p.price, ABS(order_date - start_date) as date_diff
from ord o
left join prd p on p.product = o.product


order_date
id
product
price
date_diff


2023-04-01
10001
prod_A
20
14

2023-04-01
10001
prod_A
10
0

2023-04-01
10001
prod_B
20
0

2023-04-02
10002
prod_A
20
13

2023-04-02
10002
prod_A
10
1

2023-04-02
10002
prod_B
20
1

2023-04-16
10003
prod_A
20
1

2023-04-16
10003
prod_A
10
15

2023-04-16
10003
prod_B
20
15



SELECT 9


with cte as (
  select o.*, p.price, ABS(order_date - start_date) as date_diff
  from ord o
  left join prd p on p.product = o.product
),
cte2 as (
  select *, row_number() over (partition by order_date, id, product order by date_diff asc) as rn
  from cte
)
select order_date,	id,	product,	price
from cte2
where rn = 1;




order_date
id
product
price


2023-04-01
10001
prod_A
10

2023-04-01
10001
prod_B
20

2023-04-02
10002
prod_A
10

2023-04-02
10002
prod_B
20

2023-04-16
10003
prod_A
20

2023-04-16
10003
prod_B
20



SELECT 6

order_date	id	product	price	date_diff
2023-04-01	10001	prod_A	20	14
2023-04-01	10001	prod_A	10	0
2023-04-01	10001	prod_B	20	0
2023-04-02	10002	prod_A	20	13
2023-04-02	10002	prod_A	10	1
2023-04-02	10002	prod_B	20	1
2023-04-16	10003	prod_A	20	1
2023-04-16	10003	prod_A	10	15
2023-04-16	10003	prod_B	20	15