SQL Grouping around gaps

Question

In SQL Server 2005 I have a table with data that looks something like this:

WTN------------Date  
555-111-1212  2009-01-01  
555-111-1212  2009-01-02  
555-111-1212  2009-01-03  
555-111-1212  2009-01-15  
555-111-1212  2009-01-16  
212-999-5555  2009-01-01  
212-999-5555  2009-01-10  
212-999-5555  2009-01-11

From this I would like to extract WTN, Min(Date), Max(Date) the twist is I would like to also break whenever there is a gap in the dates, so from the above data, my results should look like:

WTN------------ MinDate---- MaxDate  
555-111-1212   2009-01-01  2009-01-03  
555-111-1212   2009-01-15  2009-01-16  
212-999-5555   2009-01-01  2009-01-01  
212-999-5555   2009-01-10  2009-01-11

1. How can I do this in a SQL Select/ Group By?
2. Can this be done without a table or list enumerating the values I want to identify gaps in (Dates here)?

Answer 1

Why is everyone so dead set against using a table for this kind of thing? A table of numbers or a calendar table takes up such little space and is probably in memory if referenced enough anyway. You can also derive a numbers table pretty easily on the fly using ROW_NUMBER(). Using a numbers table can help with the understanding of the query. But here is a not-so-straightforward example, a trick I picked up from Plamen Ratchev a while back, hope it helps.

DECLARE @wtns TABLE
(
    WTN    CHAR(12),
    [Date] SMALLDATETIME
);

INSERT @wtns(WTN, [Date])
          SELECT '555-111-1212','2009-01-01'
UNION ALL SELECT '555-111-1212','2009-01-02'
UNION ALL SELECT '555-111-1212','2009-01-03'
UNION ALL SELECT '555-111-1212','2009-01-15'
UNION ALL SELECT '555-111-1212','2009-01-16'
UNION ALL SELECT '212-999-5555','2009-01-01'
UNION ALL SELECT '212-999-5555','2009-01-10' 
UNION ALL SELECT '212-999-5555','2009-01-11';

WITH x AS
(
    SELECT
     [Date],
     wtn,
     part = DATEDIFF(DAY, 0, [Date]) 
     + DENSE_RANK() OVER
     (
      PARTITION BY wtn
      ORDER BY [Date] DESC
     )
    FROM @wtns
)
SELECT 
    WTN, 
    MinDate = MIN([Date]),
    MaxDate = MAX([Date])
FROM
    x
GROUP BY 
    part,
    WTN
ORDER BY
    WTN DESC,
    MaxDate;

Answer 2

Your problem has to do with INTERVAL TYPES and a thing called PACKED NORMAL FORM of a relation.

The issues are discussed at large in "Temporal Data and the Relational Model".

Don't expect any SQL system to really help you with such problems.

Some tutorial systems notwithstanding, the only DBMS that offers decent support for such problems, and that I know of, is my own. No link because I don't want to be doing too much "plugging" here.

Answer 3

You can do this with the GROUP BY, by detecting the boundaries:

WITH    Boundaries
      AS (
          SELECT    m.WTN
                   ,m.Date
                   ,CASE WHEN p.Date IS NULL THEN 1
                         ELSE 0
                    END AS IsStart
                   ,CASE WHEN n.Date IS NULL THEN 1
                         ELSE 0
                    END AS IsEnd
          FROM      so1590166 AS m
          LEFT JOIN so1590166 AS p
                    ON p.WTN = m.WTN
                       AND p.Date = DATEADD(d, -1, m.Date)
          LEFT JOIN so1590166 AS n
                    ON n.WTN = m.WTN
                       AND n.Date = DATEADD(d, 1, m.Date)
          WHERE     p.Date IS NULL
                    OR n.Date IS NULL
         )
SELECT  l.WTN
       ,l.Date AS MinDate
       ,MIN(r.Date) AS MaxDate
FROM    Boundaries l
INNER JOIN Boundaries r
        ON r.WTN = l.WTN
           AND r.Date >= l.Date
           AND l.IsStart = 1
           AND r.IsEnd = 1
GROUP BY l.WTN
       ,l.Date