I've got two tables, orders
, and currency_rates
. I need to join orders using orders.received_at
with currency_rates.valid_from
(and also using the common column currency_id, but that's easy).
The problem I am facing is that currency_rates.valid_from
is not a full table of dates, but just start and end dates of date ranges. In practice I've got three currency periods '2019-12-01' - '2020-01-03'
, '2020-01-03' - '2020-01-09'
, '2020-01-09' - onward
. What is the most elegant way of accomplishing that in Postgres? Is it possible to do that using the Select statement? Thanks!
EDIT: Solutions without manually specifying ranges are welcomed.
EDIT2: Added a table orders_currency_rates with few example rows of the desired result.
CREATE TABLE orders
(
id BIGINT,
received_at DATE,
shipping_cost DOUBLE PRECISION,
currency_id VARCHAR,
invoice_address_id BIGINT,
delivery_address_id BIGINT
);
INSERT INTO orders (id, received_at, shipping_cost, currency_id, invoice_address_id, delivery_address_id)
VALUES (385902, '2020-01-01', 0, 'CZK', 1, 11),
(386278, '2020-01-02', 12.83, 'USD', 2, NULL),
(386279, '2020-01-03', 49.36, 'USD', 3, 12),
(386280, '2020-01-03', 12.83, 'USD', 4, 13),
(386281, '2020-01-05', 12.83, 'USD', 5, 14),
(386282, '2020-01-06', 11.43, 'GBP', 6, NULL),
(386283, '2020-01-07', 12.83, 'USD', 7, 15),
(386284, '2020-01-08', 44.03, 'EUR', 8, NULL),
(386285, '2020-01-11', 12.83, 'USD', 9, NULL),
(386286, '2020-02-12', 62.55, 'USD', 10, NULL);
CREATE TABLE currency_rates
(
currency_id VARCHAR,
rate DOUBLE PRECISION,
valid_from DATE
);
INSERT INTO currency_rates (currency_id, rate, valid_from)
VALUES ('EUR', 24.165, '2019-12-01'),
('USD', 19.359, '2019-12-01'),
('GBP', 27.039, '2019-12-01'),
('PLN', 5.5, '2019-12-01'),
('EUR', 25.2, '2020-01-03'),
('USD', 20.34, '2020-01-03'),
('GBP', 28.4, '2020-01-03'),
('PLN', 5.3, '2020-01-03'),
('EUR', 26.165, '2020-01-09'),
('USD', 21.359, '2020-01-09'),
('GBP', 29.039, '2020-01-09'),
('PLN', 5.8, '2020-01-09');
Example of the desired result for the join.
CREATE TABLE orders_currency_rates
(
id BIGINT,
received_at DATE,
shipping_cost DOUBLE PRECISION,
currency_id VARCHAR,
invoice_address_id BIGINT,
delivery_address_id BIGINT,
rate DOUBLE PRECISION,
valid_from DATE
);
INSERT INTO orders_currency_rates (id, received_at, shipping_cost, currency_id, invoice_address_id, delivery_address_id, rate, valid_from)
VALUES (386278, '2020-01-02', 12.83, 'USD', 2, NULL, 19.359, '2019-12-01'),
(386279, '2020-01-03', 49.36, 'USD', 3, 12, 20.34, '2020-01-03'),
(386286, '2020-02-12', 62.55, 'USD', 10, NULL, 21.359, '2020-01-09');