FREE AI-Enhanced Online SQL Server Compiler

Untitled*

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

DECLARE @WidgetPrice TABLE (WidgetPriceId BIGINT IDENTITY(1,1), WidgitId INT, Price MONEY, 
    StartEffectiveWhen DATE, EndEffectiveWhen DATE)

INSERT INTO @WidgetPrice(WidgitId, Price, StartEffectiveWhen, EndEffectiveWhen)
VALUES
(100,      21.48, '2020-1-1',         '2021-8-5'),
(100,      19.34, '2021-8-6',         '2021-12-31'),
(100,      19.34, '2022-1-1',         '2022-12-31'),
(100,      19.34, '2023-1-1',         '2023-1-31'),
-- There is a date gap here (No price from 2023-1-31 to 2023-3-5)
(100,      19.34, '2023-3-5',         '2023-12-31'),
(100,      12.87, '2024-1-1',         '2024-1-31'),
(100,      12.87, '2024-2-1',         '2100-12-31'),
-- Next Widget's prices          
(200,      728.25, '2020-1-1',         '2021-12-31'),
(200,      728.25, '2022-1-1',         '2022-12-31'),
(200,      861.58, '2023-1-1',         '2024-5-21'),
(200,      601.19, '2024-5-22',        '2100-12-31')

--Find interesting edges
SELECT WidgitId
	,Price
	,MIN(StartEffectiveWhen) AS StartEffectiveWhen
	,MAX(EndEffectiveWhen) AS EndEffectiveWhen
FROM (
	SELECT 
		SUM(i.StartingEdge) OVER (
			PARTITION BY WidgitId ORDER BY StartEffectiveWhen
			) AS LeadingCount
		, WidgitId
		, Price
		, StartEffectiveWhen
		, EndEffectiveWhen
	FROM (
		SELECT 
			CASE 
				WHEN ISNULL(LAG(price) OVER (
							PARTITION BY WidgitID ORDER BY StartEffectiveWhen ASC
							), - 1) != Price
					OR ISNULL(LAG(EndEffectiveWhen) OVER (
							PARTITION BY WidgitID ORDER BY StartEffectiveWhen ASC
							), '1900-01-01') != DATEADD(day, - 1, StartEffectiveWhen)
					THEN 1
				ELSE 0
				END AS StartingEdge
			, WidgitId
			, Price
			, StartEffectiveWhen
			, EndEffectiveWhen
		FROM @WidgetPrice
		) i
	) g
GROUP BY g.Price
	,g.WidgitID
	,LeadingCount

WidgitId	Price	StartEffectiveWhen	EndEffectiveWhen
100	12.8700	2024-01-01	2100-12-31
100	19.3400	2021-08-06	2023-01-31
100	19.3400	2023-03-05	2023-12-31
100	21.4800	2020-01-01	2021-08-05
200	601.1900	2024-05-22	2100-12-31
200	728.2500	2020-01-01	2022-12-31
200	861.5800	2023-01-01	2024-05-21

(11 rows affected)

(7 rows affected)