I wish you had posted DDL, which is minimal Netiquette
This is a basic history table skeleton. See if you helps. Remember 85-95% of the work in SQL is in the DDL.
CREATE TABLE PriceHistory
(ean CHAR(13) NOT NULL -- industry standard barcode
price_prev_date DATE NOT NULL,
price_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
price_end_date DATE, -- NULL means current price
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 0.0000),
-- now we add constraints
PRIMARY KEY (ean, price_start_date),
UNIQUE (ean, price_end_date),
UNIQUE (ean, prev_end_date),
--ordering of events
CHECK (price_start_date <= price_end_date),
-- Optionally, CHECK (price_start_date < price_end_date
CHECK (prev_end_date <= price_start_date),
-- Optionally, CHECK (price_start_date = price_prev_date + INTERVAL ‘1’ DAY), -- prevents gaps
-- self-referencing constraint
FOREIGN KEY (ean, prev_end_date)
REFERENCES PriceHistory (ean, price_end_date)
The first three uniqueness constraints are fairly obvious. The EAN and the start of a price have to be unique and not NULL-able so we have a natural primary key. The ending dates, current and previous, might have NULLs, so we need to use a UNIQUE constraint.
The next constraints give an ordering to each event, namely, price_prev_date is on or before price_start_date, which is on or before price_end_date. The reason for not putting this into a single BETWEEN predicate is that each constraint will have a name in production code that will show up in error messages so we want to be exact.
The self-referencing constraint is a trick from Alex Kuznetsov. It says that all the previous price ending dates were really ending dates for some time period. You will want to play with options to get them to fit your own business rules.
It is also a good idea to have a VIEW with the current data:
CREATE VIEW CurrentPrices (..)
WHERE price_end_date IS NULL
OR price_end_date >= CURRENT_TIMESTAMP;
You use a BETWEEN predicate to get the appropriate price for a given order on a particular date.
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.price_start_date
AND COALESCE (price_end_date, CURRENT_TIMESTAMP); -- or other known data as needed