add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE pricedata ([from] INT, [to] INT, price INT)
INSERT pricedata
VALUES
(2008, 2009, 100),
(2009, 2011, 121),
(2011, 2020, 142)

3 rows affected
CREATE PROC InsertPriceData (@InsertFrom INT, @InsertTo INT, @InsertPrice INT)
AS
-- Delete any row completely superseded by new insert
DELETE pricedata
WHERE [from] >= @InsertFrom
AND [to] <= @InsertTo
-- Duplicate and adjust any row split by new insert, leaving lower residual
INSERT pricedata ([from], [to], price)
SELECT [from], @InsertFrom, price
FROM pricedata
WHERE [from] < @InsertFrom
AND [to] > @InsertTo
-- Adjust original row split by new insert, leaving upper residual
UPDATE pricedata
SET [from] = @InsertTo
WHERE [from] < @InsertFrom
AND [to] > @InsertTo
-- Trim lower end of row overlapping upper end of insert
UPDATE pricedata
SET [from] = @InsertTo
WHERE [from] >= @InsertFrom
AND [from] < @InsertTo
-- Trim upper end of row overlapping lower end of insert
UPDATE pricedata
SET [to] = @InsertFrom
WHERE [to] > @InsertFrom
AND [to] <= @InsertTo

-- Now that we have resolved any overlaps, we are clear to insert the new row
INSERT pricedata ([from], [to], price)
VALUES (@InsertFrom, @InsertTo, @InsertPrice)

SELECT * FROM pricedata ORDER BY [from]

EXEC InsertPriceData 2010, 2012, 109
SELECT * FROM pricedata ORDER BY [from]

from to price
2008 2009 100
2009 2011 121
2011 2020 142
from to price
2008 2009 100
2009 2010 121
2010 2012 109
2012 2020 142
-- More tests

EXEC InsertPriceData 2011, 2025, 999 -- Trim 109 and delete 142
SELECT * FROM pricedata ORDER BY [from]

EXEC InsertPriceData 2020, 2021, 888 -- Split 999
SELECT * FROM pricedata ORDER BY [from]

EXEC InsertPriceData 2011, 2015, 777 -- Trim first 999
SELECT * FROM pricedata ORDER BY [from]

EXEC InsertPriceData 2023, 2025, 666 -- Trim upper 999
SELECT * FROM pricedata ORDER BY [from]

EXEC InsertPriceData 2012, 2024, 555 -- Trim 777, delete 999/888/999, trim 666
SELECT * FROM pricedata ORDER BY [from]

from to price
2008 2009 100
2009 2010 121
2010 2011 109
2011 2025 999
from to price
2008 2009 100
2009 2010 121
2010 2011 109
2011 2020 999
2020 2021 888
2021 2025 999
from to price
2008 2009 100
2009 2010 121
2010 2011 109
2011 2015 777
2015 2020 999
2020 2021 888
2021 2025 999
from to price
2008 2009 100
2009 2010 121
2010 2011 109
2011 2015 777
2015 2020 999
2020 2021 888
2021 2023 999
2023 2025 666
from to price
2008 2009 100
2009 2010 121
2010 2011 109
2011 2012 777
2012 2024 555
2024 2025 666