如何在具有连续日期和重复数据的记录中查找日期范围 [英] How to find date ranges in records with consecutive dates and duplicate data

问题描述

可能有任何简单的解决方案，但我看不到。我有一个包含连续日期的表，并且经常重复这些连续日期中的几个的关联数据：

There's probably any easy solution for this, but I can't see it. I have a table with consecutive dates and often duplicate associated data for several of these consecutive dates:

Date       Col1  Col2
5/13/2010  1     A
5/14/2010  1     A
5/15/2010  2     B
5/16/2010  1     A
5/17/2010  1     A
5/18/2010  3     C
5/19/2010  3     C
5/20/2010  3     C

使用MS T-SQL，我希望找到每次运行的不同Col1和Col2值的开始和结束日期：

Using MS T-SQL, I wish to find the start and end dates for each run of distinct Col1 and Col2 values:

StartDate  EndDate    Col1  Col2
5/13/2010  5/14/2010  1     A
5/15/2010  5/15/2010  2     B
5/16/2010  5/17/2010  1     A
5/18/2010  5/20/2010  3     C

假设：绝不会缺少任何日期。 Col1和Col2不为null。
有什么想法-最好不要使用游标？
非常感谢，
-alan

Assumptions: There are never any missing dates. Col1 and Col2 are not null. Any ideas - preferably that don't use cursors? Many thanks, -alan

推荐答案

对于SQL 2005+，我认为下面的方法应该可行

For SQL 2005+ I think the below should work

WITH DATES AS
(
   SELECT COL1, COL2, DATE,
      DATEADD(DAY, -1 * ROW_NUMBER() 
      OVER(PARTITION BY COL1, COL2 ORDER BY DATE), DATE) AS GRP
   FROM YOUR_TABLE
)
SELECT COL1, COL2, MIN(DATE) AS STARTDATE, MAX(DATE) AS ENDDATE
FROM DATES
GROUP BY COL1, COL2, GRP

如果有重复的记录，请使用 DENSE_RANK（）代替 ROW_NUMBER（）

If you have any duplicate records, use DENSE_RANK() instead of ROW_NUMBER()

对于SQL 2000，涉及一个子查询和一个相关查询。

For SQL 2000 there is a sub query and a co-related query involved.

SELECT COL1, COL2, MIN(DATE) AS STARTDATE, MAX(DATE) AS ENDDATE
FROM (SELECT COL1, COL2, DATE,
    (SELECT MIN(DATE)
     FROM YOUR_TABLE B
     WHERE B.DATE >= A.DATE AND B.COL1 = A.COL1 AND B.COL2 = A.COL2
           AND NOT EXISTS
           (SELECT *
            FROM YOUR_TABLE C
            WHERE C.COL1 = B.COL1 AND C.COL2 = B.COL2
            AND DATEDIFF(DAY, B.DATE, C.DATE) = 1)
    ) AS GRP
    FROM YOUR_TABLE A
)
GROUP BY COL1, COL2, GRP

这篇关于如何在具有连续日期和重复数据的记录中查找日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！