FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

-- Setup the 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

-- The code below does it, but it needs to loop (and an unknown amout of times).  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)

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

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

(5 rows affected)
Warning: Null value is eliminated by an aggregate or other SET operation.

(2 rows affected)

(2 rows affected)

(1 rows affected)

(2 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(0 rows affected)

(1 rows affected)

(0 rows affected)

(0 rows affected)

(0 rows affected)

(0 rows affected)

(4 rows affected)