Finding Islands of Consecutive Performance - long winded version (without the row-row in single statement)

IslandsOfConsecutivePerf.sql

/*******************************************************
identifying gaps on a month  
*******************************************************/
if object_id('tempdb..#accounts') is not null
	drop table #accounts;

-- Starting Point
select
	'Starting Result Set'	as query_description
	,x.account
	,x.eom
	,x.result_description
into #accounts
from
	(
	values
	  (55002, '2014-01-31', 'no gaps on this account')
	, (55002, '2014-02-28', 'no gaps on this account')
	, (55002, '2014-03-31', 'no gaps on this account')
	, (55002, '2014-04-30', 'no gaps on this account')
	, (62522, '2014-01-31', 'Consecutive Group #1 from 1/31/2014 to 2/28/2014')
	, (62522, '2014-02-28', 'Consecutive Group #1 from 1/31/2014 to 2/28/2014')
	, (62522, '2014-04-30', 'Consecutive Group #2 from 4/30/2014 to 6/30/2014')
	, (62522, '2014-05-31', 'Consecutive Group #2 from 4/30/2014 to 6/30/2014')
	, (62522, '2014-06-30', 'Consecutive Group #2 from 4/30/2014 to 6/30/2014')
	, (62522, '2014-08-31', 'Consecutive Group #3 from 8/31/2014 to 9/30/2014')
	, (62522, '2014-09-30', 'Consecutive Group #3 from 8/31/2014 to 9/30/2014')
	) x (account, eom, result_description)

select
	*
from
	#accounts a

/*******************************************************
IDENTIFY MONTH INTEGER VALUE, SO WE CAN DO BASIC MATH ON IT  
*******************************************************/

select
	query_description =	'Month Int Is Calculated'
	,a.account
	,a.eom
	,a.result_description
	,month_int =		datediff(month, 0, a.eom) -- get the calculated months from 0 so we have an integer value to add/minus, and moving from dec to jan won't be a problem since it's just months counting
from
	#accounts a


/*******************************************************
ROW NUMBER TO SHOW THE PARTITIONING  
*******************************************************/
if object_id('tempdb..#AccountsRanked') is not null
	drop table #AccountsRanked;

select
	query_description =	'Dense_rank() shows the sort'
	,a.account
	,a.eom
	,a.result_description
	,month_int =		datediff(month, 0, a.eom) -- get the calculated months from 0 so we have an integer value to add/minus, and moving from dec to jan won't be a problem since it's just months counting
	,sqid =				dense_rank() over (partition by a.account order by a.eom asc)
into #AccountsRanked
from
	#accounts a

/*******************************************************
LOOK AT THE CALCULATED GROUP AFTER TAKING MONTH_INT - THE DENSE_RANK()  
*******************************************************/
select
	query_description =	'Grouper_id (not a fish) shows the basic math that creates the group'
	,a.account
	,a.eom
	,a.result_description
	,a.month_int
	,a.sqid
	,grouper_id =		a.month_int - a.sqid
from
	#AccountsRanked a


/*******************************************************
NOT BROKEN INTO MULTIPLE PIECES IT CAN BE DONE VERY QUICKLY  
*******************************************************/


select
	'Showing Single Statement Grouping'	as query_description
	,a.account
	,a.eom
	,grouper_id =						c.month_int - dense_rank() over (partition by a.account order by c.month_int)
from
	#accounts a

	cross apply (
		select
			month_int =
			datediff(month, 0, a.eom)
	) c


/*******************************************************
NOW USING THIS RESULT WE CAN USE A COUPLE STATEMENTS TO GET START/ENDS EASILY  
*******************************************************/


;
with ctegrouper as (


			select
				a.account
				,a.eom
				,grouper_id =	c.month_int - dense_rank() over (partition by a.account order by c.month_int)
			from
				#accounts a

				cross apply (
					select
						month_int =
						datediff(month, 0, a.eom)
				) c

		),
	cteranges as (
			select
				g.account
				,g.grouper_id
				,date_earliest =	min(g.eom)
				,date_oldest =		max(g.eom)
			from
				ctegrouper g
			group by
				g.account
				,g.grouper_id

		)


select
	account
	,grouper_id
	,date_earliest
	,date_oldest
	,consecutive_months =	datediff(month, date_earliest, date_oldest)
from
	cteranges