• Could probably be simpler but this separates out the steps.

    declare @t table (s varchar(10), dt datetime)

    insert @t select 'A', '20120101'

    insert @t select 'A', '20120102'

    insert @t select 'A', '20120102 01:00'

    insert @t select 'A', '20120103'

    insert @t select 'A', '20120105'

    insert @t select 'A', '20120106'

    insert @t select 'A', '20120106'

    insert @t select 'B', '20120101'

    insert @t select 'B', '20120103'

    insert @t select 'B', '20120104'

    ;with cte as

    (

    select *, dte = dateadd(dd,datediff(dd,0,dt),0) from @t

    ) ,

    cte2 as

    (

    select *, num = (select count(distinct t2.dte) from cte t2 where t2.dte<=t1.dte and t1.s=t2.s) ,

    gap = case when exists (select * from cte t3 where t3.dte = t1.dte-1 and t1.s = t3.s) then 0 else 1 end

    from cte t1

    )

    select * ,

    val = num - (select max(num) from cte2 t2 where t2.dte<=t1.dte and t2.gap=1 and t1.s=t2.s)+1

    from cte2 t1


    Cursors never.
    DTS - only when needed and never to control.