Selecting from a single date column and creating multiple start and end records

Question

I really wanted to solve this problem but there comes a point when you have to just put it out there, learn and move on.

I have a temporary table which contains 1 column, POCDates, which is a list of dates, some of which are continuous/contiguous. For example:

POCDates
01/01/2014
02/01/2014
03/01/2014
04/01/2014
10/03/2014
11/03/2014
25/03/2014
26/03/2014
28/03/2014

I am looking to run a query in my procedure that will select from this temporary table and, using the sample data above, display the following:

POCDatesSummary
Start        End         Count
01/01/2014   04/01/2014  4
10/03/2014   11/03/2014  2
25/03/2014   26/03/2014  2
28/03/2014   28/03/2014  1

The count isn't essential, but I suspect that this is the most simple part using a DATEDIFF so I've left it in there.

I have tried a number of approaches and trawled forums for similar problems; nothing has come close. Hope I can find the answer here.

maybe this will help you in the right direction: http://stackoverflow.com/questions/13566303/how-to-group-subsequent-rows-based-on-a-criteria-and-then-count-them-mysql — fellowworldcitizen, Oct 15 '14 at 13:32
What version of SQL Server are you using? That can make the answer simpler. And you want to group the days into continuous ranges? — Code Different, Oct 15 '14 at 13:59
SQL Server 2008 (I've updated the tag). Simply put, yes, when a group of days are continuous, I require them to be grouped as a range in some way or form, ideally presented as the example outcome in the post so further analysis can be conducted. — cope, Oct 15 '14 at 14:05

score 0 · Accepted Answer · answered Oct 15 '14 at 14:20

Use recursive CTE:

;WITH cte1 AS (
    SELECT  POCDates, ROW_NUMBER() OVER (ORDER BY POCDates) AS RowNumber
    FROM    MyTable
), cte2 AS (
    SELECT  POCDates,
            POCDates AS RangeStart,
            POCDates AS RangeEnd,
            RowNumber
    FROM    cte1
    WHERE   RowNumber = 1
    UNION ALL
    SELECT      c1.POCDates,
                CASE
                    WHEN c1.POCDates = DATEADD(day,1,c2.POCDates) THEN c2.RangeStart
                    ELSE c1.POCDates
                END             AS RangeStart,
                c1.POCDates     AS RangeEnd,
                c1.RowNumber
    FROM        cte2 c2
    INNER JOIN  cte1 c1 ON c1.RowNumber = c2.RowNumber + 1
) 

SELECT  RangeStart          AS [Start],
        MAX(RangeEnd)       AS [End],
        COUNT(RangeStart)   AS [Count]
FROM    cte2
GROUP BY RangeStart
OPTION  (MAXRECURSION 0)

MAXRECURSION 0 allows the CTE to recur infinitely. The default value is 10, which will cause the query to fail if your table has more than 10 rows.

Impressive level of abstract thinking here, thank you for taking the time to write this. A few more tests to see if it stands up to rigour but all seems well so far. Forza! — cope, Oct 15 '14 at 15:56

Selecting from a single date column and creating multiple start and end records

1 Answers1