Trying to join two tables together to have the unit_price vary based on the day

I have some code drafted but I’m not sure how to finish it, or if it needs more work. It looks like this.

select p.product_id, p.product_name, pp.effective_date, pp.unit_price
from products p
inner join product_price pp
on p.product_id = pp.product_id
where (....stuck here)

Everything looks good, but how do I get the price to be based on the given effective date? The given effective date is 01/01/2021. I was thinking something maybe like this for the where:

where effective_date = 01/01/2021;

I’m not sure if this is correct though. I tried running it and similar where statements but nothing came of it.

Sample data:

products

P_id, p_name
11  ,  boys shorts

product_price

p_id, eff_date , unit_price
11  , 01-JAN-21, 10.48

I want the product’s price to now be a calculation based on a given date, using a join between Products and Product_Price.

[

Answer

When price data is stored with only a single date, it typically means “use this price from this date onward, but only up to the next date_effective”.

The window function row_number() assigns an integer to each row, within a related over clause, partition by controls what is used to re-start numbering at 1 and an order by determines the sequence of rows and numbers within the partition. So by using a descending date order the most recent date per partition can be identified with a row number of 1.

If you couple this technique with a filter on the effective date being less than or equal to a given date, then the price that applies on the given date will be returned.:

select p.product_id, p.product_name, pp.effective_date, pp.unit_price
from products p
inner join (
           select
               product_price.*
             , row_number() over(partition by product_id order by effective_date DESC) as rn
           from product_price
           where effective_date <= to_date('01/01/2021','dd/mm/yyyy')
           ) pp on p.product_id = pp.product_id
            and pp.rn = 1 /* only the relevant date/price is returned */

The second join condition could be used as a where condition instead

select p.product_id, p.product_name, pp.effective_date, pp.unit_price
from products p
inner join (
           select
               product_price.*
             , row_number() over(partition by product_id order by effective_date DESC) as rn
           from product_price
           where effective_date <= to_date('01/01/2021','dd/mm/yyyy')
           ) pp on p.product_id = pp.product_id
where pp.rn = 1 /* only the relevant date/price is returned */

Sample Data

CREATE TABLE PRODUCTS ( 
PRODUCT_ID      INTEGER, 
PRODUCT_NAME VARCHAR(100));

CREATE TABLE PRODUCT_PRICE ( 
PRODUCT_ID     INTEGER, 
EFFECTIVE_DATE DATE , 
UNIT_PRICE     DECIMAL(12,3));

INSERT INTO PRODUCTS VALUES(100,'Product: 100');
INSERT INTO PRODUCTS VALUES(3412,'Product: 3412');
INSERT INTO PRODUCTS VALUES(1034,'Product: 1034');
INSERT INTO PRODUCTS VALUES(775522,'Product: 775522');
INSERT INTO PRODUCTS VALUES(101,'Product: 101');
INSERT INTO PRODUCTS VALUES(2342,'Product: 2342');
INSERT INTO PRODUCTS VALUES(8643,'Product: 8643');
INSERT INTO PRODUCTS VALUES(99771,'Product: 99771');
INSERT INTO PRODUCTS VALUES(2324,'Product: 2324');
INSERT INTO PRODUCTS VALUES(103,'Product: 102');

INSERT INTO PRODUCT_PRICE VALUES(100, TO_DATE('2020-09-01','YYYY-MM-DD'),10.12);
INSERT INTO PRODUCT_PRICE VALUES(101, TO_DATE('2020-09-01','YYYY-MM-DD'),10.12);
INSERT INTO PRODUCT_PRICE VALUES(103, TO_DATE('2020-09-01','YYYY-MM-DD'),10.12);
INSERT INTO PRODUCT_PRICE VALUES(1034, TO_DATE('2020-09-01','YYYY-MM-DD'),10.12);
INSERT INTO PRODUCT_PRICE VALUES(2342, TO_DATE('2020-09-01','YYYY-MM-DD'),10.12);
INSERT INTO PRODUCT_PRICE VALUES(3412, TO_DATE('2020-09-01','YYYY-MM-DD'),10.12);

INSERT INTO PRODUCT_PRICE VALUES(100, TO_DATE('2021-01-01','YYYY-MM-DD'),10.43);
INSERT INTO PRODUCT_PRICE VALUES(101, TO_DATE('2020-11-11','YYYY-MM-DD'),10.43);
INSERT INTO PRODUCT_PRICE VALUES(103, TO_DATE('2021-01-01','YYYY-MM-DD'),10.43);
INSERT INTO PRODUCT_PRICE VALUES(1034, TO_DATE('2020-11-11','YYYY-MM-DD'),10.43);
INSERT INTO PRODUCT_PRICE VALUES(2342, TO_DATE('2021-01-01','YYYY-MM-DD'),10.43);
INSERT INTO PRODUCT_PRICE VALUES(3412, TO_DATE('2020-12-01','YYYY-MM-DD'),10.43);
INSERT INTO PRODUCT_PRICE VALUES(8643, TO_DATE('2021-01-01','YYYY-MM-DD'),10.43);
INSERT INTO PRODUCT_PRICE VALUES(99771, TO_DATE('2021-02-01','YYYY-MM-DD'),10.43);
INSERT INTO PRODUCT_PRICE VALUES(2324, TO_DATE('2021-01-01','YYYY-MM-DD'),10.43);
INSERT INTO PRODUCT_PRICE VALUES(775522, TO_DATE('2021-03-01','YYYY-MM-DD'),10.43);

Results

+------------+---------------+----------------------+------------+
| PRODUCT_ID | PRODUCT_NAME  |    EFFECTIVE_DATE    | UNIT_PRICE |
+------------+---------------+----------------------+------------+
|        100 | Product: 100  | 2021-01-01T00:00:00Z |      10.43 |
|        101 | Product: 101  | 2020-11-11T00:00:00Z |      10.43 |
|        103 | Product: 102  | 2021-01-01T00:00:00Z |      10.43 |
|       1034 | Product: 1034 | 2020-11-11T00:00:00Z |      10.43 |
|       2324 | Product: 2324 | 2021-01-01T00:00:00Z |      10.43 |
|       2342 | Product: 2342 | 2021-01-01T00:00:00Z |      10.43 |
|       3412 | Product: 3412 | 2020-12-01T00:00:00Z |      10.43 |
|       8643 | Product: 8643 | 2021-01-01T00:00:00Z |      10.43 |
+------------+---------------+----------------------+------------+

see this working demo at http://sqlfiddle.com/#!4/8a6c9/2