Today's price is not necessarily the price that applied when an order was placed. A price history table records each effective start so the correct historical value can be selected. The start boundary and its uniqueness are part of the data model.
Model a Price History Table With One Start per Product
The history table needs ProductID, EffectiveFrom, and Price. A unique key on ProductID and EffectiveFrom prevents two competing prices from starting at the same instant. Without that guarantee, TOP one can choose an arbitrary tied row unless another business priority is defined.
I treat the unique start as a correctness constraint, not just a performance index. An invoice needs one unambiguous applicable price. Sorting tied records more consistently would make an unresolved business conflict repeatable, but it would not explain which price should win.
Use datetime2 when prices can change within a day. The sample uses explicit midnight timestamps for readability. Currency and region are omitted to keep the example narrow. Include them in the real history key if each combination can have its own independently effective price.
CREATE TABLE #PriceHistory
(ProductID int NOT NULL,EffectiveFrom datetime2(0) NOT NULL,
Price decimal(12,2) NOT NULL,
PRIMARY KEY CLUSTERED(ProductID,EffectiveFrom));
CREATE TABLE #PriceOrders
(OrderID int PRIMARY KEY,ProductID int NOT NULL,OrderDate datetime2(0) NOT NULL);
INSERT #PriceHistory VALUES
(10,'2026-01-01T00:00:00',20),(10,'2026-06-01T00:00:00',25),
(10,'2026-09-01T00:00:00',30),(20,'2026-03-01T00:00:00',40);
INSERT #PriceOrders VALUES
(1,10,'2026-05-31T12:00:00'),(2,10,'2026-06-01T00:00:00'),
(3,20,'2026-02-01T00:00:00'),(4,20,'2026-04-01T00:00:00');Select the Latest Start Not After the Order
The applicable row has an effective start less than or equal to the order time. Ordering those candidates descending places the latest applicable start first. TOP one then selects it. The key's uniqueness makes that choice deterministic for each product.
SELECT o.OrderID,o.ProductID,o.OrderDate,p.EffectiveFrom,p.Price
FROM #PriceOrders o
CROSS APPLY
(
SELECT TOP(1) EffectiveFrom,Price FROM #PriceHistory p
WHERE p.ProductID=o.ProductID AND p.EffectiveFrom<=o.OrderDate
ORDER BY p.EffectiveFrom DESC
) p
ORDER BY o.OrderID;CROSS APPLY removes an order without an applicable history row. That is a useful behavior only when the consumer explicitly wants priced orders alone. For an invoice-preparation process, a missing price should normally be exposed and handled, not silently omitted from the workload.
The sample includes one order before its product's first price. Inspect which rows the query returns. Compare them with the original orders rather than accepting a successful query as proof that every order received a price.
Preserve Orders With Missing Prices
OUTER APPLY retains the order when its lookup has no match. The returned price fields are NULL. This makes missing history visible to an exception report. Do not convert that NULL price to zero unless a reviewed business rule specifically authorizes a zero price.
SELECT o.OrderID,o.ProductID,o.OrderDate,p.Price,
CASE WHEN p.EffectiveFrom IS NULL THEN 'Missing price'
ELSE 'Price found' END AS PriceStatus
FROM #PriceOrders o
OUTER APPLY
(
SELECT TOP(1) EffectiveFrom,Price FROM #PriceHistory p
WHERE p.ProductID=o.ProductID AND p.EffectiveFrom<=o.OrderDate
ORDER BY p.EffectiveFrom DESC
) p
ORDER BY o.OrderID;I keep the missing-price status explicit through the calling workflow. A NULL result tells you the model lacks an applicable row. The application must decide whether to reject, defer, or request a reviewed correction. SQL cannot invent the business price from the nearest future entry.
Derive End Boundaries With LEAD
SQL Server 2012 and later support LEAD for obtaining the next start within each product. Treat that next start as the exclusive end of the current price interval. A NULL next start means the latest price remains effective indefinitely under this model.
WITH Periods AS
(
SELECT ProductID,EffectiveFrom,Price,
LEAD(EffectiveFrom) OVER
(PARTITION BY ProductID ORDER BY EffectiveFrom) AS EffectiveTo
FROM #PriceHistory
)
SELECT o.OrderID,o.OrderDate,p.EffectiveFrom,p.EffectiveTo,p.Price
FROM #PriceOrders o
LEFT JOIN Periods p ON p.ProductID=o.ProductID
AND o.OrderDate>=p.EffectiveFrom
AND (o.OrderDate<p.EffectiveTo OR p.EffectiveTo IS NULL)
ORDER BY o.OrderID;The lower bound is inclusive and the upper bound is exclusive. At exactly the next start, the new price applies. BETWEEN would include both endpoints and can match two adjacent intervals. That boundary error can duplicate an order and corrupt a later total.
The LEAD query assumes every row applies until the next start. If the business allows explicit gaps or overlapping validity periods, the model needs additional rules and checks. Do not derive continuous coverage from a history table whose contract permits inactive intervals.
Index the Price History Table for the Lookup
The clustered primary key begins with ProductID and EffectiveFrom. It supports seeking the product and the relevant date range. SQL Server can scan an ordered index backward for a descending latest-start lookup, so a separate descending index is not automatically necessary.
For a permanent table with another clustered key, a nonclustered index on ProductID and EffectiveFrom can include Price. Review the actual plan for the target query. Check whether the lookup stops after the first qualifying row and whether a sort or extra lookup adds unnecessary work.
An index improves access but cannot repair ambiguous effective starts. Keep uniqueness enforced at the full business key. If currency or customer group participates in the price rule, the lookup predicate and unique key must both include it.
Distinguish Historical Reconstruction From Invoicing
A history query reconstructs the price using the rows currently stored in the history table. A later backdated correction can change that reconstruction. An issued invoice can require preserving the actual accepted unit price independently of future history edits.
Decide whether the system stores the resolved price on the invoice line, records the chosen history version, or uses another auditable design. The price table alone does not define immutability of commercial documents. A historical query is a calculator, not a time machine with locked paperwork.
Which result does the reader need: the price rule currently applicable to a past date, or the value actually charged then? Those can differ after corrections. Make the distinction explicit before allowing a scheduled report to recalculate old amounts.
Test Price History Table Boundaries and Corrections
Test an order just before a change, exactly at the change, and just after it. Add a product with no history and one with only a future price. Verify the unique key rejects competing starts through a controlled test, without adding invalid data to the real table.
Also test a backdated insertion in a disposable copy. It changes the intervals before the next existing start. Review how that affects previously priced orders and whether the application permits the change. Concurrent price maintenance and order creation need a transaction and audit design appropriate to the business.
Keep timestamps in a defined time zone. Datetime2 has no stored offset, so cross-zone ordering needs a documented conversion policy. Preserve that policy alongside the effective-start contract. Correct date comparison depends on comparing values with the same meaning, not merely the same SQL type.
A price history table needs a clear correction policy for backdated entries. Keep resolved invoice values auditable rather than assuming current history rows always reproduce the original charge.
Related reading on this blog: CROSS APPLY and OUTER APPLY in Everyday Queries and Preventing Overlapping Date Ranges in a Table.
A historical price lookup is not a request for the newest price, it is a request for the latest unambiguous start that does not exceed the order time.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
