CODEWITHSUNDEEP
sql-serversql-server-2008
Jorge Rodríguez Galán
Jorge Rodríguez Galán

Reputation: 11

SQL group by date difference with previous row

I looking for some grouping using datetime daily rows to build date range intervals

My table is something like:

id | A  | B  | Date
1  | 1  | 2  | 1/10/2010
2  | 1  | 2  | 2/10/2010
3  | 1  | 2  | 3/10/2010
4  | 1  | 3  | 4/10/2010
5  | 1  | 3  | 5/10/2010
6  | 1  | 2  | 6/10/2010
7  | 1  | 2  | 7/10/2010
8  | 1  | 2  | 8/10/2010

My first try was:

SELECT A, B, MIN(DATE), MAX(date)
FROM table
GROUP BY A, B

So after group by A, B and use min and max with date on my select, I get invalid results due the repetition of B = 2.

     A    B   Date                 A  B   min(Date)  max(Date)
1  | 1  | 2  | 1/10/2010           1  2 | 1/10/2010  8/10/2010
2  | 1  | 2  | 2/10/2010  Invalid
3  | 1  | 2  | 3/10/2010  ------->
6  | 1  | 2  | 6/10/2010
7  | 1  | 2  | 7/10/2010
8  | 1  | 2  | 8/10/2010

I'm looking for how to calculate the third member of the group by... So the expected intervals results:

     A    B   Start Date    End Date 
.. | 1  | 2 | 1/10/2010  | 3/10/2010
.. | 1  | 3 | 4/10/2010  | 5/10/2010 
.. | 1  | 2 | 6/10/2010  | 8/10/2010

I need to support SQL Server 2008

Thank you in advance for your help

Upvotes: 0

Views: 1332

Answers (1)

ZLK
ZLK

Reputation: 2884

The following is an easy way to deal with "islands and gaps" where you need to find gaps in consecutive dates:

SELECT A, B, StartDate = MIN([Date]), EndDate = MAX([Date])
FROM
(
    SELECT *,
        RN = DATEDIFF(DAY, 0, [Date]) - ROW_NUMBER() OVER (PARTITION BY A, B ORDER BY [Date])
    FROM myTable
) AS T
GROUP BY A, B, RN;

To break it down into slightly simpler-to-understand logic: you assign each date a number (DATEDIFF(DAY, 0, [Date]) here) and each date a row number (partitioned by A and B here), then any time there's a gap in the dates, the difference between those two will change.

There are a variety of resources you can use to understand different approaches to "islands and gaps" problems. Here is one that might help you with tackling other varieties of this in the future: https://www.red-gate.com/simple-talk/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/

Upvotes: 2

Related Questions