Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Grouping around gaps

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)?
like image 417
automatic Avatar asked Oct 19 '09 18:10

automatic


People also ask

Can you GROUP BY multiple things in SQL?

The SQL GROUP BY clause is used along with some aggregate functions to group columns that have the same values in different rows. The group by multiple columns technique is used to retrieve grouped column values from one or more tables of the database by considering more than one column as grouping criteria.

Can you GROUP BY coalesce?

Because the COALESCE is an expression, you can use it in any clause that accepts an expression such as SELECT , WHERE , GROUP BY , and HAVING .

What is the grouping syntax in SQL?

Syntax: SELECT column1, function_name(column2) FROM table_name WHERE condition GROUP BY column1, column2 HAVING condition ORDER BY column1, column2; function_name: Name of the function used for example, SUM() , AVG().


Video Answer


1 Answers

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;
like image 196
Aaron Bertrand Avatar answered Oct 04 '22 07:10

Aaron Bertrand