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
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.