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.
GROUP BY ActiveYN
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.