How to count consecutive days and group by year

Question

I want to count consecutive days (rows) and that is fairly easy (given all the answers to similar questions). But in my data set I have groups of consecutive rows with dates such as:

1. 30/12/2010
2. 31/12/2010
3. 01/01/2011
4. 02/01/2011

Looks like a one group (4 consecutive days), but I would like to split this group into two groups. So when having:

1. 30/12/2010
2. 31/12/2010
3. 01/01/2011
4. 02/01/2011
5. 05/01/2011
6. 06/02/2011
7. 07/02/2011

I would like to see this grouped into four groups (not three):

1. 30/12/2010
2. 31/12/2010

3. 01/01/2011
4. 02/01/2011

5. 05/01/2011

6. 06/02/2011
7. 07/02/2011

I'm using SQL Server 2014

So is the rule that no group should extend over multiple years? — Damien_The_Unbeliever, Nov 10 '16 at 08:19
@Salman, this would only work if there was only one group of consecutive days in a month, but let's take into consideration that there can be multiple groups in a month — Mateusz Wit, Nov 10 '16 at 08:57

score 1 · Accepted Answer · edited May 23 '17 at 11:59

You can number your rows like this:

DECLARE @T TABLE(id INT, dt DATE);

INSERT INTO @T VALUES
(1, '2010-12-30'),
(2, '2010-12-31'),
(3, '2011-01-01'),
(4, '2011-01-02'),
(5, '2011-01-05'),
(6, '2011-02-06'),
(7, '2011-02-07');

WITH CTE1 AS (
    SELECT *, YEAR(dt) AS temp_year, ROW_NUMBER() OVER (ORDER BY dt) AS temp_rownum
    FROM @T
), CTE2 AS (
    SELECT CTE1.*, DATEDIFF(DAY, temp_rownum, dt) AS temp_dategroup
    FROM CTE1
)
SELECT *, RANK() OVER (ORDER BY temp_year, temp_dategroup) AS final_rank
FROM CTE2
ORDER BY final_rank, dt

Result:

id  dt          temp_year  temp_rownum  temp_dategroup  final_rank
1   2010-12-30  2010       1            40539           1
2   2010-12-31  2010       2            40539           1
3   2011-01-01  2011       3            40539           3
4   2011-01-02  2011       4            40539           3
5   2011-01-05  2011       5            40541           5
6   2011-02-06  2011       6            40572           6
7   2011-02-07  2011       7            40572           6

It is possible to use simplify the query but I chose to display all columns so that it is easier to understand. The DATEDIFF trick was copied from this answer.

How to count consecutive days and group by year

1 Answers1