计算多行中的最大连续月份

一尘不染

计算多行中的最大连续月份

sql

我试图计算开始日期结束日期的多行中表中连续月份的最大数目。日期范围不需要是完全连续的，只有月份需要是连续的。例如，如果客户的日期范围为01/01/2012-06/01/2012和07/01/2012-08/31/2012，则我们将6月和7月计算为连续8个月。

示例数据如下。

基本表 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

应该可以创建一个子表，每个月都有一个循环，然后在该表中循环寻找连续的月，但是这涉及两个循环，我希望避免。

阅读 167

2021-05-30

共1个答案

一尘不染

如果一个风俗不能有重叠的日期，那么这个问题就很容易了。首先，根据每笔记录查看我们有多少个月的时间：

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

然后得到最大

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

由于流行的需求，这里是如何使用递归CTE的方法-请注意，此代码建立在上面的代码上，因此在您深入之前请了解它。您还必须了解递归CTE的工作方式。

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

小提琴：http
://sqlfiddle.com/#!6/eee59/14

有关此解决方案的一些注意事项

开始日期必须早于结束日期（可以更改以解决此问题）
日期不能重叠（可以更改以解决此问题）
您可能需要使用连接，因为这取决于您的规范…如前所述，如果开始日期和结束日期在同一个月内，则此方法将无效-但也许应该吗？但是请记住要小心，具有无限递归的很容易。

2021-05-30