# Grabbing Consecutive Months Without Using Cursor

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!

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.

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

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!

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;
``````

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..

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