By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
declare @test table (
CustomerId varchar(10),
Type INT,
date datetime
)
insert into @test
values
('aaaa', 1,'2015-10-24 22:52:47'),
('bbbb', 1,'2015-10-23 22:56:47'),
('cccc', 2,'2015-10-22 21:52:47'),
('dddd', 2,'2015-10-20 22:12:47'),
('aaaa', 1,'2015-10-19 20:52:47'),
('dddd', 2,'2015-10-18 12:52:47'),
('aaaa', 3,'2015-10-18 12:52:47')
;
with cte as (
select *, newtype = case when Type <> lag(Type) over(order by date desc) then 1 else 0 end
from @test
)
select *,
Result = sum(newtype) over(
order by date desc
rows between unbounded preceding and current row
)
from cte
order by date desc
| CustomerId | Type | date | newtype | Result |
|---|---|---|---|---|
| aaaa | 1 | 2015-10-24 22:52:47.000 | 0 | 0 |
| bbbb | 1 | 2015-10-23 22:56:47.000 | 0 | 0 |
| cccc | 2 | 2015-10-22 21:52:47.000 | 1 | 1 |
| dddd | 2 | 2015-10-20 22:12:47.000 | 0 | 1 |
| aaaa | 1 | 2015-10-19 20:52:47.000 | 1 | 2 |
| dddd | 2 | 2015-10-18 12:52:47.000 | 1 | 3 |
| aaaa | 3 | 2015-10-18 12:52:47.000 | 1 | 4 |