Query To Identify Contiguous Ranges

I'm trying to write a query on the below data set to add a new column which has some sort of 'period_id_group'. contiguous new_period row_nr new_period_starting_id 0 0

Solution 1:

So, if I'm understanding you correctly, you just need one additional column.

SELECT t1.contiguous, t1.new_period, t1.row_nr, t1.new_period_starting_id,
    (SELECT TOP 1 (new_period_starting_id) 
     FROM YourTable t2
     WHERE t2.row_nr <= t1.row_nr
         AND t2.period_id_group >0/* optimization */ORDERBY t2.row_nr DESC/* optimization */) AS period_id_group
FROM YourTable t1

Solution 2:

Here is yet another option for this.

selectt1.contiguous
    , t1.new_period
    , t1.row_nr
    , t1.new_period_starting_id
    , x.new_period_starting_idfrom @tmp2t1outerapply
(
    select top 1 *
    from @tmp2 t2
    where (t2.row_nr = 1or t2.new_period_starting_id > 0)
        and t1.row_nr >= t2.row_nr
    order by t2.row_nr desc
) x

Solution 3:

Found the solution:

select*
    , casewhen contiguous =0then f1
        when contiguous =1and new_periods =1then f1
        when contiguous =1and new_periods =0then v
        elseNULLend [period_group]
from (
    select*
        , (selectmax(f1) from #temp2 where new_period_starting_id >0and rn < t1.rn) [v]
    from #temp2 t1
    ) rs
orderby rn