Grabbing Consecutive Months Without Using Cursor

Transact-SQL
1

Hello, trying to get consecutive months for orders by user. So, with data that looks like this:
user order dates
1 5/1/2015
1 6/1/2015
1 7/1/2015
1 9/1/2015
1 11/1/2015
1 12/1/2015
1 1/1/2016
1 12/1/2015
2 8/1/2015
2 9/1/2015
2 11/1/2015

Only user 1 will be returned in the query results. Users only need to have one, three-month consecutive run. So, User 2 has no consecutive runs, whereas user 1 has two consecutive runs, but one is sufficient so that all I need returned in the query results is that user.

I do not want to do this recursively, there is too much data. I did check for solutions online, but they all had variations/other criteria and since I am by no means an advanced user, I could not tease out what was applicable and what wasn't. In fact just looking at their solutions from an overview, I found the concept/methodology of the solution a bit difficult.

Any help and explanation of what's going on is appreciated!

2

First the whole query:

with cte([user],months)
  as (select [user]
            ,datediff(month,0,dates)
        from yourtable
       group by [user]
               ,datediff(month,0,dates)
     )
select a.[user]
  from cte as a
       inner join cte as b
               on b.[user]=a.[user]
              and b.months=a.months+1
       inner join cte as c
               on c.[user]=a.[user]
              and c.months=a.months+2
 group by a.[user]
;

Now, we break it apart.
"datediff(month,0,dates)" gives you number of months after 1900-01-01.
We group by user and datediff, which gives us a one row per user per month.
Then we join the list of rows 3 times to see if we have 3 consecutive months, and finaly we group by user, so we avoid duplicate user.
Hope this helps.

3

Thanks a lot. I will take a hard look at this tomorrow to see if I understand what its doing. Much appreciated!

4

Hi bitsmed, looks like your query worked - I am doing qa on it now. However, wanted to know if there is a way to make a temp table from the results?

Thanks again for your help!

5

Something like this:

create table #tmptbl(
   [user] int
  ,months int
  ,primary key([user],months)
);

insert into #tmptbl([user],months)
select [user]
      ,datediff(month,0,dates)
  from yourtable
 group by [user]
         ,datediff(month,0,dates)
;

select a.[user]
  from #tmptbl as a
       inner join #tmptbl as b
               on b.[user]=a.[user]
              and b.months=a.months+1
       inner join #tmptbl as c
               on c.[user]=a.[user]
              and c.months=a.months+2
 group by a.[user]
;

drop table #tmptbl;
6

Thanks bitsmed, sorry this is a bit late, but was out for a while..also, I tried to give a LIKE to your reply as it was everything I needed, but I cannot see on this webpage where I can do that. I think it may have to do with internet explorer. I've seen this issue before where not all the commands and what not are available on a web page with certain browsers..in this case IE. I have asked for Firefox, maybe that will enable me to LIKE your reply..

7

No worries - important thing is that you got the query to work :slight_smile: