Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

BIT Barbarian

After working in the mortgage industry for 7 years, I transitioned into Business Intelligence and began learning SQL and .NET. My goal is to integrate my business knowledge into my development to intelligently analyze and find solutions to problems. Blogging offers me an option to share what I've learned as well as receive feedback on better practices and solutions.

Finding Groups - Consecutive Months

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.

/*******************************************************
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

Comments

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...