By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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, '2023-1-1', '2100-12-31'),
('OtherWidget', 13.24, 1, '2014-1-1', '2100-12-31');
SELECT * FROM #Price;
| DataId | NameOfWidget | Price | PriceScheduleId | StartEffectiveWhen | EndEffectiveWhen |
|---|---|---|---|---|---|
| 1 | CoolWidget | 3.5100 | 1 | 2015-01-01 | 2021-12-31 |
| 2 | CoolWidget | 2.0000 | 2 | 2017-01-01 | 2022-12-31 |
| 3 | CoolWidget | 4.2300 | 1 | 2021-01-01 | 2100-12-31 |
| 4 | CoolWidget | 2.0000 | 2 | 2023-01-01 | 2100-12-31 |
| 5 | OtherWidget | 13.2400 | 1 | 2014-01-01 | 2100-12-31 |
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 |