user20907 user20907 - 10 months ago 68
SQL Question

SQL Server Count Sequential Breakpoints

I have a SQL Server 2012 table that stores whether a customer is Active for a particular week. I want to output a results that shows me how many weeks in a row a customer was active along with start week and end week. I have the below code but that only gives me total number of weeks a customer was active. Is it possible to break the counts in sequence? Any help is appreciated.


enter image description here


This is called a gaps-and-islands problem. One solution uses a difference of row numbers:

select customer, activeyn,
       min(startweek), max(startweek)
from (select t.*,
             (row_number() over (partition by customer order by startweek) -
              row_number() over (partition by customer, activeyn order by startweek)
             ) as grp
      from tablea t
     ) t
group by customer, activeyn, grp;

The calculation looks a bit like a magical incantation. But, if you run the subquery and look at the results for each row_number() calculation, you'll understand why the difference defines the groups you are looking for.