sql - Count maximum continuous months across multiple rows -

i attempting count maximum number of continuous months in table across multiple rows of start dates end dates. date ranges not need continuous, months need continuous. example, if customer has date range of 01/01/2012 - 06/01/2012, , 07/01/2012 - 08/31/2012, count june , july, resulting in 8 continuous months.

sample data below.

base table customerid startdate enddate ---------- ---------- ---------- 1001 01/01/2012 06/30/2012 1001 07/01/2012 08/31/2012 1001 01/01/2013 07/31/2013 1002 01/01/2012 06/01/2012 1002 07/01/2012 08/31/2012 1003 01/01/2012 05/31/2012 1003 07/01/2012 08/31/2012 1004 01/01/2012 02/28/2012 1004 03/01/2012 07/31/2012 1004 08/01/2012 08/31/2012

results customerid maxcontinuous ---------- ------------- 1001 8 1002 8 1003 5 1004 8

it should possible create sub-table every single month involved loop, , loop through table looking continuous months, involves 2 loops, prefer avoid.

if custom can't have overlapping dates problem easy. first every record @ how many months have:

 select customerid, abs(datediff('month',startdate,enddate)) months   basedata 

then max

 select customeid, max(months) maxcontig  (    select customerid, abs(datediff('month',startdate,enddate)) months     basedata  ) sub 

due popular demand here how recursive cte -- note, code builds on code above understand before dive in. have know how recursive ctes work.

with rangelist  (    select customerid,           startdate,            enddate,           datediff(month,startdate,enddate)+1 months     customers     union     select r.customerid,            r.startdate,           bd.enddate,           datediff(month,r.startdate,bd.enddate)+1 months     rangelist r    join customers bd       on r.customerid = bd.customerid ,         month(dateadd(month,1,r.enddate)) = month(bd.startdate) ,         year(dateadd(month,1,r.enddate)) = year(bd.startdate)  )  select customerid, max(months) maxcontig  rangelist  group customerid 

fiddle: http://sqlfiddle.com/#!6/eee59/14

some notes solution

• startdate must before enddate (it changed fix this)
• dates must not overlap (it changed fix this)
• you need play around join since depends on specifications... pointed out, won't work if start , end date in same month -- maybe should? remember careful however, easy have stackoverflow infinite recursion.