Count Maximum Continuous Months Across Multiple Rows

I am attempting to count the maximum number of continuous months in a table across multiple rows of start dates end dates. The date ranges do not need to be completely continuous,

Solution 1:

If a custom can't have overlapping dates then this problem is easy. First for every record look at how many months we have:

SELECT CustomerID, ABS(DATEDIFF('MONTH',StartDate,EndDate)) as Months 
 FROM BaseData

Then get the max

SELECT CustomeID, MAX(Months) AS MaxContig
 FROM (
   SELECT CustomerID, ABS(DATEDIFF('MONTH',StartDate,EndDate)) as Months 
   FROM BaseData
 ) sub

Due to popular demand here is how to do it with a recursive CTE -- note, this code builds on the code above so understand that before you dive in. You also have to know how recursive CTEs work.

WITH rangeList AS
 (
   SELECT CustomerID,
          StartDate, 
          EndDate,
          DATEDIFF(month,StartDate,EndDate)+1as Months 
   FROM Customers

   UNIONALLSELECT R.CustomerID, 
          R.StartDate,
          BD.EndDate,
          DATEDIFF(month,R.StartDate,BD.EndDate)+1as Months 
   FROM rangeList R
   JOIN Customers BD 
     ON R.CustomerID = BD.CustomerID ANDMonth(DATEADD(month,1,R.EndDate)) =Month(BD.StartDate) ANDYear(DATEADD(month,1,R.EndDate)) =Year(BD.StartDate)
 )
 SELECT CustomerID, Max(Months) as MaxContig
 FROM rangeList
 GROUPBY CustomerID

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

Some notes about this solution

• startdate must be before enddate (It could be changed to fix this)
• dates must not overlap (It could be changed to fix this)
• you probably need to play around with the join since this depends on your specifications... as was pointed out, this won't work if the start and end date are in the same month -- but maybe they should? Remember to be careful however, it is easy to have stackoverflow with infinite recursion.