Sql-server – Count rows where value differs

countsql server

I have the following dataset in SQL Server:

Name     Pattern
-------  -------
Peter    DRF  
Peter    DRF
Peter    BBB
Peter    DRF
Peter    CCC
Peter    CCC
Sue      DRF
Sue      CCC
Sue      DRF
Sue      VVV
Sue      SSS

I need to count the number of times a pattern changes per Name (not only distinct changes). So my end result will be:

Peter 3    
Sue 4

In other words, only counting the distinct changes. Peter changed from DRF to BBB to CCC and Sue changed from DRF to CCC to DRF to VVV to SSS.

Is this possible without using a cursor?

I have a start date and end date per row, but no unique id. Patterns gets logged by the system per person per day and sometimes multiple times per day per person.

Best Answer

First you need row_number/serial number.

;With CTE as
(
select *
,row_number()over(order by name)rn
 from @t
)

select t.name
,sum(case when t1.rn=t.rn +1 and t.pattern<>t1.pattern then 1 else 0 end)Result
from cte t
cross apply(select t1.rn,t1.pattern from cte t1
where t.name=t1.name
)t1
group by t.name

if output is ok across all data then it will perform ok.

Related Question