Creating a Date Range from Multiple Rows Based on a Single Date

Solution (SQL 2005): Using a Self-JOIN

There are probably other ways to establish an exact match for the JOIN criteria of a self-JOIN that would be compatible back to SQL 2000, but we’ll simplify the task somewhat by using ROW_NUMBER(), which was introduced in SQL 2005.


-- Solution (SQL 2005): Using a self-JOIN (ROW_NUMBER establishes exact match criteria)

WITH ProductPrices AS
(
    SELECT ProductID, EffectiveStartDT
        ,rn=ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY EffectiveStartDT)
    FROM dbo.ProductPrices
-- add filtering Condition
)
SELECT a.ProductID, a.EffectiveStartDT, EffectiveEndDT=b.EffectiveStartDT
FROM ProductPrices a
LEFT JOIN ProductPrices b ON a.ProductID = b.ProductID AND a.rn =b.rn-1
-- add filtering Condition
ORDER BY a.ProductID, a.EffectiveStartDT;


Result :
ProductID EffectiveDate        EffectiveEndDT
7103         2016-12-20           2017-01-01
7103        2017-01-01           2017-01-03
7103        2017-01-03           2017-03-28
7103        2017-03-28           2017-04-04
7103        2017-04-04           NULL