Here is the updated code:

;

WITH unique_dates AS (

SELECT DISTINCT p.Price, d.dt, d.is_start

FROM #prices p

CROSS APPLY (VALUES(p.date_from, 1), (DATEADD(DAY, 1, p.date_to), 0)) d(dt, is_start)

)

, packed_dates AS (

SELECT ud.price, ud.dt, ud.is_start,

CASE

WHEN LEAD(ud.dt, 1, '9999') OVER(PARTITION BY ud.price ORDER BY ud.dt, ud.is_start) = ud.dt THEN 1

WHEN LAG(ud.dt, 1, '1900') OVER(PARTITION BY ud.price ORDER BY ud.dt, ud.is_start) = ud.dt THEN 1

END AS exclusion_flag

FROM unique_dates ud

)

, date_ranges AS (

SELECT pd.dt AS date_from, LEAD(pd.dt) OVER(ORDER BY pd.dt) AS date_to

FROM packed_dates pd

WHERE pd.exclusion_flag IS NULL

)

SELECT dr.date_from, DATEADD(DAY, -1, dr.date_to) AS date_to, MIN(p.price) AS price

FROM date_ranges dr

INNER JOIN #prices p

ON p.date_from < dr.date_to

AND p.date_to >= dr.date_from

WHERE dr.date_from < dr.date_to

GROUP BY dr.date_from, dr.date_to

;

I've split the first CTE into two parts. The first one finds the unique values that I mentioned, the second one sets the exclusion flags.

Drew