The maximum number of months in a few lines

Question

I am trying to calculate the maximum number of continuous months in a table over several lines of end dates of start dates. Ranges of dates should not be completely continuous, only months should be continuous. For example, if a client has a date range 01/01/2012 - 06/01/2012 and 07/01/2012 - 08/31/2012, then we look forward to June and 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

CustomerID MaxContinuous ---------- ------------- 1001 8 1002 8 1003 5 1004 8

It should be possible to create a subtable with each month associated with the cycle, and then scroll through this table in search of continuous months, however this is due to two cycles that I would prefer to avoid.

Answer 1

If the user cannot have overlapping dates, this problem will be simple. First, for each entry, see how many months we have:

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

Then we get max

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

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

 WITH rangeList AS ( SELECT CustomerID, StartDate, EndDate, DATEDIFF(month,StartDate,EndDate)+1 as Months FROM Customers UNION ALL SELECT R.CustomerID, R.StartDate, BD.EndDate, DATEDIFF(month,R.StartDate,BD.EndDate)+1 as Months FROM rangeList R JOIN Customers BD ON R.CustomerID = BD.CustomerID AND Month(DATEADD(month,1,R.EndDate)) = Month(BD.StartDate) AND Year(DATEADD(month,1,R.EndDate)) = Year(BD.StartDate) ) SELECT CustomerID, Max(Months) as MaxContig FROM rangeList GROUP BY CustomerID 

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

Some notes about this solution.

• startdate should be before enddate (it could have been changed to fix this)
• dates should not overlap (this can be changed to fix this)
• you probably need to play around with the introduction, as it depends on your specifications ... as stated, this will not work if the start and end dates are in the same month - but maybe they should? However, remember to be careful, it is easy to have stackoverflow with infinite recursion.