Get All Windows between Start and End Dates

Question

Here is the SQL Fiddle for my question: https://sqlfiddle.com/sql-server/online-compiler?id=ab1634d7-fec7-4918-ac1c-3f4fcac8dc92

I have the following sample data:

DROP TABLE IF EXISTS #Price
CREATE TABLE #Price (DataId INT IDENTITY(1,1), NameOfWidget VARCHAR(50), Price MONEY, PriceScheduleId INT, 
                     StartEffectiveWhen DATE, EndEffectiveWhen DATE)

INSERT INTO #Price (NameOfWidget, Price, PriceScheduleId, StartEffectiveWhen, EndEffectiveWhen)
VALUES
    ('CoolWidget', 3.51, 1, '2015-1-1', '2021-12-31'),
    ('CoolWidget', 2.00, 2, '2017-1-1', '2022-12-31'),
    ('CoolWidget', 4.23, 1, '2021-1-1', '2100-12-31'),
    ('CoolWidget', 2.00, 2, '2021-1-1', '2100-12-31'),
    ('OtherWidget', 13.24, 1, '2014-1-1', '2100-12-31')

I now need to get this data into the following format:

NameOfWidget    StartEffectiveWhen  EndEffectiveWhen
CoolWidget      2015-01-01          2016-12-31
CoolWidget      2017-01-01          2021-12-31
CoolWidget      2021-01-01          2022-12-31
CoolWidget      2023-01-01          2100-12-31
OtherWidget     2015-01-01          2100-12-31

This follows the following logic, grouped by NameOfWidget:

1. Finds the lowest StartEffectiveWhen.
2. Finds the next lowest StartEffectiveWhen or EndEffectiveWhen. That date becomes the next EndEffectiveWhen. But if it was an EndEffectiveWhen, then we subtract one day from it.
3. It then repeats the above steps, except it excludes the already used data.

The goal is to have a row for each and every time frame "window".

The code below does what I need, but it uses a loop to do it.

As usual, my real data is much more complex. It also has 56 million rows. (The code below takes 3 hours to run on my real data)

I am hoping for a way to do what I have below without needing to loop.

---

My (Slow, Loop Based) Code

DROP TABLE IF EXISTS #EffectiveRange
CREATE TABLE #EffectiveRange (EffectiveDateId INT IDENTITY(1,1), StartEffectiveWhen DATE, EndEffectiveWhen DATE, EndWhenOfRowsThatMatchStartDate DATE, SecondStartWhen DATE, NameOfWidget VARCHAR(50), CalculationRound INT)

DECLARE @CalculationRound INT = 1

-- This is < 15 in my real code
WHILE (@CalculationRound < 5)
BEGIN

    -- Find the first/next range in source price table.
    INSERT INTO  #EffectiveRange(StartEffectiveWhen, EndWhenOfRowsThatMatchStartDate, SecondStartWhen, NameOfWidget, CalculationRound)
    SELECT  MIN(price.StartEffectiveWhen) StartWhen, NULL, NULL, price.NameOfWidget, @CalculationRound
    FROM    #Price price            
    WHERE   price.StartEffectiveWhen > 
            (SELECT MAX(maxValue.StartWhen) 
             FROM 
                (SELECT MAX(rangesSub.StartEffectiveWhen) AS StartWhen
                 FROM   #EffectiveRange AS rangesSub
                 WHERE  rangesSub.NameOfWidget = price.NameOfWidget
                 UNION ALL
                 SELECT CAST('1/1/1900' AS DATE) AS StartWhen) AS maxValue)
    GROUP BY price.NameOfWidget 

    -- Find the end date for the rows that match the start date we just found.
    UPDATE  #EffectiveRange SET
       EndWhenOfRowsThatMatchStartDate = calc.EndWhenOfRowsThatMatchStartDateCalc
    FROM 
        (
            SELECT  MIN(price.EndEffectiveWhen) AS EndWhenOfRowsThatMatchStartDateCalc, price.NameOfWidget
            FROM    #Price price
                    JOIN #EffectiveRange ranges
                        ON ranges.NameOfWidget = price.NameOfWidget
                        AND ranges.CalculationRound = @CalculationRound
            WHERE   price.StartEffectiveWhen = ranges.StartEffectiveWhen
            GROUP BY price.NameOfWidget
        ) AS calc
        JOIN #EffectiveRange ranges
            ON ranges.NameOfWidget = calc.NameOfWidget
            AND ranges.CalculationRound = @CalculationRound

    -- Find the next largest start date for the calculation round.        
    UPDATE  #EffectiveRange SET
        SecondStartWhen = calc.SecondStartWhen
    FROM 
        (
            SELECT  MIN(price.StartEffectiveWhen) SecondStartWhen, price.NameOfWidget
            FROM    #Price price
                    JOIN #EffectiveRange ranges
                        ON ranges.NameOfWidget = price.NameOfWidget
                        AND ranges.CalculationRound = @CalculationRound
            WHERE   price.StartEffectiveWhen > ranges.StartEffectiveWhen
            GROUP BY price.NameOfWidget
        ) AS calc
        JOIN #EffectiveRange ranges
            ON ranges.NameOfWidget = calc.NameOfWidget
            AND ranges.CalculationRound = @CalculationRound

    -- Send the EndWhen to be the lesser of EndWhenOfRowsThatMatchStartDate and secondStartDate.  
    -- This will define our window of effectiveness for this round of the test. (once we have all of the windows (aka each time a change was made),
    -- we will caclulate the price for each window.
    UPDATE #EffectiveRange SET
        EndEffectiveWhen = IIF((EndWhenOfRowsThatMatchStartDate < SecondStartWhen) OR SecondStartWhen IS NULL, EndWhenOfRowsThatMatchStartDate, DATEADD(DAY, -1, SecondStartWhen))
    WHERE   CalculationRound = @CalculationRound

    SET @CalculationRound = @CalculationRound + 1
END

-- Show the final result
SELECT  ranges.NameOfWidget, ranges.StartEffectiveWhen, ranges.EndEffectiveWhen
FROM    #EffectiveRange ranges
ORDER BY ranges.NameOfWidget, ranges.StartEffectiveWhen


DROP TABLE IF EXISTS #EffectiveRange
DROP TABLE IF EXISTS #Price

---

Update

This SQL Fiddle shows what I ended up doing:

https://sqlfiddle.com/sql-server/online-compiler?id=b0d81632-b14d-4374-a80e-0835750f48bc

@Akina got me thinking about my issue in the right direction. (Thanks @Akina!)

Just incase, here is the query that I ended up using:

DROP TABLE IF EXISTS #Price
CREATE TABLE #Price (DataId INT IDENTITY(1,1), NameOfWidget VARCHAR(50), Price MONEY, PriceScheduleId INT, StartEffectiveWhen DATE, EndEffectiveWhen DATE)

INSERT INTO #Price (NameOfWidget, Price, PriceScheduleId, StartEffectiveWhen, EndEffectiveWhen)
VALUES
    ('CoolWidget', 3.51, 1, '2015-1-1', '2021-12-31'),
    ('CoolWidget', 2.00, 2, '2017-1-1', '2022-12-31'),
    ('CoolWidget', 4.23, 1, '2021-1-1', '2100-12-31'),
    ('CoolWidget', 2.00, 2, '2021-1-1', '2100-12-31'),
    ('OtherWidget', 13.24, 1, '2014-1-1', '2018-5-4'),
    ('OtherWidget', 13.24, 1, '2018-5-6', '2019-12-31'),
    ('OtherWidget', 13.24, 1, '2020-1-1', '2100-12-31')

;WITH OrderedDates AS 
(
    SELECT  priceStart.NameOfWidget, priceStart.StartEffectiveWhen AS DateWhen, 1 AS IsStartDate, 0 AS IsEndDate
    FROM    #Price priceStart

    UNION 

    SELECT  priceStart.NameOfWidget, priceStart.EndEffectiveWhen AS DateWhen, 0 AS IsStartDate, 1 AS IsEndDate
    FROM    #Price priceStart
    
)
SELECT  OrderedDates.NameOfWidget,
        CASE 
            WHEN LAG(OrderedDates.DateWhen) OVER (PARTITION BY OrderedDates.NameOfWidget ORDER BY OrderedDates.DateWhen) IS NULL THEN '1900-1-1'
            WHEN LAG(OrderedDates.IsStartDate ) OVER (PARTITION BY OrderedDates.NameOfWidget ORDER BY OrderedDates.DateWhen) = 1 
                THEN  LAG(OrderedDates.DateWhen) OVER (PARTITION BY OrderedDates.NameOfWidget ORDER BY OrderedDates.DateWhen)
            ELSE DATEADD(DAY, 1, LAG(OrderedDates.DateWhen) OVER (PARTITION BY OrderedDates.NameOfWidget ORDER BY OrderedDates.DateWhen))
        END AS StartEffectiveWhen, 
        
        CASE
            WHEN OrderedDates.IsEndDate = 1 THEN OrderedDates.DateWhen
            ELSE DATEADD(DAY, -1, OrderedDates.DateWhen)
        END AS EndEffectiveWhen
FROM    OrderedDates
ORDER BY OrderedDates.NameOfWidget

Answer 1

SELECT NameOfWidget,
       StartEffectiveWhen,
       CASE WHEN EndEffectiveWhen >= LEAD(StartEffectiveWhen) OVER (PARTITION BY NameOfWidget ORDER BY StartEffectiveWhen) 
            THEN DATEADD(day, -1, LEAD(StartEffectiveWhen) OVER (PARTITION BY NameOfWidget ORDER BY StartEffectiveWhen))
            ELSE EndEffectiveWhen
            END EndEffectiveWhen
FROM #Price t1
ORDER BY 1,2;

NameOfWidget	StartEffectiveWhen	EndEffectiveWhen
CoolWidget	2015-01-01	2016-12-31
CoolWidget	2017-01-01	2020-12-31
CoolWidget	2021-01-01	2022-12-31
CoolWidget	2023-01-01	2100-12-31
OtherWidget	2014-01-01	2100-12-31

fiddle

PS. Source data is fixed, ('CoolWidget', 2.00, 2, '2021-1-1', '2100-12-31') is altered to ('CoolWidget', 2.00, 2, '2023-1-1', '2100-12-31').