This one, does return proper results, However, on the given dataset sample, it performs 3 scans & 26 logical reads (incl. 24 reads of CTE worktable) vs 34 scans but only 7 logical reads. If you double the test data, then the CTE method will perform twice as more of reads while the number of reads using "quirky update" will stay the same. Inreasing the volume of data will stop SQL to perform logical reads of CTE worktable and number of reads in "quirky update" increases.

I guess, it should be tested on the real set of data to check what performs best.

I also think, that with proper indices (for example unique clustered on Name and Date), it is possible to achieve the acceptable performance of the version (revised & fixed) which uses APPLY, and, from my personal point of view is the most qute one :-D:

select bs.Name

,bs.Price as StartPrice

,Min(bs.Date) as StartDT

,etp.NextPrice

,Min(etp.EndDT) as EndDT

from #Temp bs

outer apply (select top 1 et.Name

,et.Price as NextPrice

,et.Date as EndDt

from #Temp et

where et.Name = bs.Name and et.Price != bs.Price and et.Date > bs.Date

order by et.Date asc

) etp

group by bs.Name, bs.Price, etp.NextPrice

order by bs.Name, StartDT