nigelrivett (12/12/2012)

---

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