• I was using window functions, and this appeared to work, but it's a small sample size, so perhaps there's a flaw here:

    SELECT t.*

    FROM ( SELECT top 10

    id

    , start

    , 'prev id' = LEAD(id) OVER (PARTITION BY p1 ORDER BY updatedate)

    , 'prev start' = LEAD(enddate) OVER (PARTITION BY p1 ORDER BY updatedate)

    , updatedate

    FROM #test AS t

    ORDER BY updatedate desc) a

    INNER JOIN #test t ON t.id IN (a.id, a.[prev id])

    WHERE a.updatedate > '20150701'

    AND a.updatedate < '20150720'

    Some extra columns in there, just to show what's happening.