By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE product (id INT, num INT, price INT);
INSERT INTO product VALUES
(1 , 1001 , 100),
(2 , 1030 , 110),
(3 , 2010 , 150);
SELECT * FROM product;
CREATE TABLE product_audit (id INT, rev INT, price INT);
INSERT INTO product_audit VALUES
(1 , 1 , 200),
(1 , 3 , 175),
(1 , 6 , 100),
(2 , 2 , 100),
(2 , 7 , 110),
(3 , 4 , 130),
(3 , 5 , 120),
(3 , 8 , 150);
SELECT * FROM product_audit;
CREATE TABLE revinfo (id INT, `timestamp` DATE);
INSERT INTO revinfo VALUES
(1 , '2020-08-02'),
(2 , '2020-09-25'),
(3 , '2020-09-26'),
(4 , '2020-11-12'),
(5 , '2020-12-20'),
(6 , '2021-01-08'),
(7 , '2021-01-09'),
(8 , '2021-01-23');
SELECT * FROM revinfo;
| id | num | price |
|---|---|---|
| 1 | 1001 | 100 |
| 2 | 1030 | 110 |
| 3 | 2010 | 150 |
| id | rev | price |
|---|---|---|
| 1 | 1 | 200 |
| 1 | 3 | 175 |
| 1 | 6 | 100 |
| 2 | 2 | 100 |
| 2 | 7 | 110 |
| 3 | 4 | 130 |
| 3 | 5 | 120 |
| 3 | 8 | 150 |
| id | timestamp |
|---|---|
| 1 | 2020-08-02 |
| 2 | 2020-09-25 |
| 3 | 2020-09-26 |
| 4 | 2020-11-12 |
| 5 | 2020-12-20 |
| 6 | 2021-01-08 |
| 7 | 2021-01-09 |
| 8 | 2021-01-23 |
SELECT *
FROM product
JOIN product_audit ON product_audit.id = product.id
JOIN revinfo ON revinfo.id = product_audit.rev
ORDER BY num, `timestamp`;
| id | num | price | id | rev | price | id | timestamp |
|---|---|---|---|---|---|---|---|
| 1 | 1001 | 100 | 1 | 1 | 200 | 1 | 2020-08-02 |
| 1 | 1001 | 100 | 1 | 3 | 175 | 3 | 2020-09-26 |
| 1 | 1001 | 100 | 1 | 6 | 100 | 6 | 2021-01-08 |
| 2 | 1030 | 110 | 2 | 2 | 100 | 2 | 2020-09-25 |
| 2 | 1030 | 110 | 2 | 7 | 110 | 7 | 2021-01-09 |
| 3 | 2010 | 150 | 3 | 4 | 130 | 4 | 2020-11-12 |
| 3 | 2010 | 150 | 3 | 5 | 120 | 5 | 2020-12-20 |
| 3 | 2010 | 150 | 3 | 8 | 150 | 8 | 2021-01-23 |
/*
Assume we are looking for prices on 2020-11-31, it would return:
num CurrentPrice OldPrice
--------------------------------------
1001 100 175
1030 110 100
2010 150 130
*/
SET @date := '2020-11-30'; -- November 31 - not exists
WITH cte AS ( SELECT product.num,
product.price CurrentPrice,
product_audit.price OldPrice,
ROW_NUMBER() OVER (PARTITION BY product.num
ORDER BY revinfo.`timestamp` DESC) rn
FROM product
JOIN product_audit ON product_audit.id = product.id
JOIN revinfo ON revinfo.id = product_audit.rev
WHERE revinfo.`timestamp` <= @date
)
SELECT num, CurrentPrice, OldPrice
FROM cte
WHERE rn = 1;
| num | CurrentPrice | OldPrice |
|---|---|---|
| 1001 | 100 | 175 |
| 1030 | 110 | 100 |
| 2010 | 150 | 130 |