Finding Groups - Consecutive Months
sql-server
A step by step explanation on one way to get a consecutive period of months, which could easily be adapted to days, years, or other values. I’ll continue on this track and post a tutorial on eliminating overlapping dates soon.
Finding Islands of Consecutive Performance
- long winded version (without the row-row in single statement)
/*******************************************************
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