i

Description

An island seen from the sky

Source

Chief Master Sergeant Don Sutherland, U.S. Air Force · original page

Licence

PD Public domain

Original photo, unretouched. The background is a version of it stylised by the site.

Continuous ranges (islands)

The problem that seems to require state remembered from row to row, and that is solved by a grouping.

On this page

1. Problem statement
2. Data set
3. Procedural versus set-based
   1. Procedural
   2. Set-based
4. Expected result
5. Why set-based is preferable
6. Pitfalls
7. Variants
8. Verifying equivalence
9. SQL Server versions

Problem statement#

For each user, list the ranges of consecutive days during which they logged in (start, end, number of days).

Data set#

CREATE TABLE dbo.Connexions
(
    ConnexionId   int IDENTITY(1,1) PRIMARY KEY,
    UtilisateurId int          NOT NULL,
    DateConnexion datetime2(0) NOT NULL
);

INSERT INTO dbo.Connexions (UtilisateurId, DateConnexion)
VALUES (1, '2026-03-01 08:00'), (1, '2026-03-02 09:30'), (1, '2026-03-03 07:45'),
       (1, '2026-03-03 18:10'),                       -- 2nd login on the same day
       (1, '2026-03-06 10:00'), (1, '2026-03-07 11:20'),
       (2, '2026-03-01 12:00'), (2, '2026-03-05 12:00');

Procedural versus set-based#

DECLARE @R TABLE (UtilisateurId int, Debut date, Fin date);
DECLARE @U int, @J date, @UPrec int = NULL, @Debut date, @Fin date;

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT DISTINCT UtilisateurId, CAST(DateConnexion AS date) AS Jour
    FROM   dbo.Connexions
    ORDER BY UtilisateurId, Jour;
OPEN c;
FETCH NEXT FROM c INTO @U, @J;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @UPrec IS NULL OR @U <> @UPrec OR @J <> DATEADD(DAY, 1, @Fin)
    BEGIN
        IF @UPrec IS NOT NULL INSERT INTO @R VALUES (@UPrec, @Debut, @Fin);
        SET @Debut = @J;
    END;
    SET @Fin = @J;
    SET @UPrec = @U;
    FETCH NEXT FROM c INTO @U, @J;
END;
IF @UPrec IS NOT NULL INSERT INTO @R VALUES (@UPrec, @Debut, @Fin);
CLOSE c;
DEALLOCATE c;

SELECT * FROM @R ORDER BY UtilisateurId, Debut;

WITH J AS
(
    SELECT DISTINCT UtilisateurId, CAST(DateConnexion AS date) AS Jour
    FROM   dbo.Connexions
),
G AS
(
    SELECT UtilisateurId, Jour,
           DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY UtilisateurId ORDER BY Jour), Jour) AS Groupe
    FROM   J
)
SELECT UtilisateurId,
       MIN(Jour) AS Debut,
       MAX(Jour) AS Fin,
       COUNT(*)  AS NbJours
FROM   G
GROUP BY UtilisateurId, Groupe
ORDER BY UtilisateurId, Debut;

Expected result#

Why set-based is preferable#

• The trick: for consecutive days, the difference between the date and its rank (ROW_NUMBER) is constant within a range and changes as soon as there is a gap. That value therefore identifies the range.
• The "sequential" problem ("I remember the previous row") boils down to a grouping: the state is no longer carried by variables, it is in the data.
• The procedural version has to handle the transitions itself (start, end, change of user); the slightest inattention there produces an edge-case bug.

Pitfalls#

• Duplicate days: the J CTE uses DISTINCT to keep only one row per day and per user; without it, the rank advances without the date advancing and the range is broken.
• Date type: work at the desired grain (here the day) before computing the rank.
• Gap tolerance: the strict technique detects consecutive days. To tolerate a gap, use the LAG variant.

Variants#

-- Ranges tolerating one missing day (gap ≤ 2 days between two logins)
WITH J AS (SELECT DISTINCT UtilisateurId, CAST(DateConnexion AS date) AS Jour FROM dbo.Connexions),
P AS (SELECT UtilisateurId, Jour, LAG(Jour) OVER (PARTITION BY UtilisateurId ORDER BY Jour) AS Prec FROM J),
F AS (SELECT UtilisateurId, Jour,
             CASE WHEN Prec IS NULL OR DATEDIFF(DAY, Prec, Jour) > 2 THEN 1 ELSE 0 END AS Rupture
      FROM P),
G AS (SELECT UtilisateurId, Jour,
             SUM(Rupture) OVER (PARTITION BY UtilisateurId ORDER BY Jour ROWS UNBOUNDED PRECEDING) AS Groupe
      FROM F)
SELECT UtilisateurId, MIN(Jour) AS Debut, MAX(Jour) AS Fin
FROM   G
GROUP BY UtilisateurId, Groupe;

Verifying equivalence#

A change of method is only worthwhile if the result is identical. Compare in both directions; no rows should come out.

-- Run in the same batch as the procedural version (the @R variable must exist)
WITH J AS (SELECT DISTINCT UtilisateurId, CAST(DateConnexion AS date) AS Jour FROM dbo.Connexions),
G AS (SELECT UtilisateurId, Jour, DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY UtilisateurId ORDER BY Jour), Jour) AS Groupe FROM J),
E AS (SELECT UtilisateurId, MIN(Jour) AS Debut, MAX(Jour) AS Fin FROM G GROUP BY UtilisateurId, Groupe)
SELECT UtilisateurId, Debut, Fin FROM @R
EXCEPT
SELECT UtilisateurId, Debut, Fin FROM E;
-- Then the reverse. No rows expected.

SQL Server versions#

ROW_NUMBER: SQL Server 2005; LAG and ROWS frames (variant): SQL Server 2012.