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(LagPrice, - 1) != Price
					OR ISNULL(LagDate, '1900-01-01') != DATEADD(day, - 1, StartEffectiveWhen)
					THEN 1
				ELSE 0
				END AS StartingEdge
			,WidgitId
			,Price
			,StartEffectiveWhen
			,EndEffectiveWhen
		FROM (
			SELECT LAG(price) OVER (
					PARTITION BY WidgitID ORDER BY StartEffectiveWhen ASC
					) AS LagPrice
				,LAG(EndEffectiveWhen) OVER (
					PARTITION BY WidgitID ORDER BY StartEffectiveWhen ASC
					) AS LagDate
				,*
			FROM @WidgetPrice
			) p
		) 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