当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Find date ranges between large gaps and ignore smaller gaps

问题描述:

I have a column of a mostly continous unique dates in ascending order. Although the dates are mostly continuos, there are some gaps in the dates of less than 3 days, others have more than 3 days.

I need to create a table where each record has a start date and an end date of the range that includes a gap of 3 days or less. But a new record has to be generated if the gap is longer than 3 days.

so if dates are:

 1/2/2012

1/3/2012

1/4/2012

1/15/2012

1/16/2012

1/18/2012

1/19/2012

I need:

 1/2/2012 1/4/2012

1/15/2012 1/19/2012

网友答案:

You can do something like this:

WITH CTE_Source AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY DT) RN
    FROM dbo.Table1
)
,CTE_Recursion AS 
(
    SELECT *, 1 AS Grp 
    FROM CTE_Source
    WHERE RN = 1

    UNION ALL

    SELECT src.*, CASE WHEN DATEADD(DD,3,rec.DT) < src.DT THEN rec.Grp + 1 ELSE Grp END AS Grp
    FROM CTE_Source src
    INNER JOIN CTE_Recursion rec ON src.RN = rec.RN +1 
)   
SELECT 
MIN(DT) AS StartDT, MAX(DT) AS EndDT 
FROM CTE_Recursion
GROUP BY Grp

First CTE is just to assign continuous numbers for all rows in order to join them later. Then using recursive CTE you can join on each next row assigning groups if date difference is larger than 3 days. In the end just group by grouping column and select desired results.

SQLFiddle DEMO

分享给朋友:
您可能感兴趣的文章:
随机阅读: