A Date Range JOIN matches a record when its date falls between a start date and an end date stored in another table.
Many business rules change over time, such as employee assignments, prices, exchange rates, and account statuses.
Find the price that was effective on each order date.
SELECT
o.OrderID,
o.OrderDate,
o.ProductID,
p.Price
FROM Orders o
INNER JOIN ProductPrices p
ON o.ProductID = p.ProductID
AND o.OrderDate >= p.StartDate
AND o.OrderDate < p.EndDate;Used in pricing, financial analysis, historical reporting, employee assignments, and effective-dated business rules.
- Date ranges can be used as JOIN conditions.
- Start and end boundaries must be defined carefully.
- Half-open ranges can prevent overlapping periods.
- Historical data often requires effective dates.
- Overlapping ranges can create duplicate matches.
What is a Date Range JOIN?
It matches records when a date falls within a start and end date range.
Why can overlapping date ranges be a problem?
A single record may match multiple ranges and produce duplicate results.