Sql-server – Finding gaps-and-islands with repeating group numbers

gaps-and-islandssql server

We have a MS-SQL dataset of tests run in various groupings and need to uniquely identify the groups

sr is the device identifier
tx is the TrackingIndex (insert order)
id is the loop order of each grouped sr

The table looks like:

sr   |tx     |id
-----+-------+---
0027 |9748116|  1
0027 |9748117|  1
0027 |9751933|  1
0027 |9751934|  1
0027 |9751935|  1
01672|9813195|  1
01672|9813196|  2
01672|9813197|  3
01767|9794370|  1
01767|9794374|  2
01767|9794378|  3
01767|9794382|  4
01767|9794386|  5
01767|9813181|  1
01767|9813182|  1

I would like to see a column added like:

sr   |tx     |id | grp
-----+-------+---+---
0027 |9748116|  1| 1
0027 |9748117|  1| 2
0027 |9751933|  1| 3
0027 |9751934|  1| 4
0027 |9751935|  1| 5
01672|9813195|  1| 6
01672|9813196|  2| 6
01672|9813197|  3| 6
01767|9794370|  1| 7
01767|9794374|  2| 7
01767|9794378|  3| 7
01767|9794382|  4| 7
01767|9794386|  5| 7
01767|9813181|  1| 8
01767|9813182|  1| 9

I have been trying to use the Island method, but am not able to create the above column. Most solutions expect the "id" number to be one continuous count with holes. Ours should always start at one.

SELECT *, ROW_NUMBER() OVER (ORDER BY tx) - id AS grp
FROM S

In the long run I may just change our insert to create a grouped column. Regardless, I will have to recreate it for all existing entries.

Best Answer

I think next sentence returns your expected result.

Since id marks loop order for each group, you can SUM() all id's with a value of 1.

select   sr, tx, id, 
         sum(case when id = 1 then 1 else 0 end) over (order by sr, tx) as grp
from     test
order by sr, tx;
GO

sr    |      tx | id | grp
:---- | ------: | -: | --:
0027  | 9748116 |  1 |   1
0027  | 9748117 |  1 |   2
0027  | 9751933 |  1 |   3
0027  | 9751934 |  1 |   4
0027  | 9751935 |  1 |   5
01672 | 9813195 |  1 |   6
01672 | 9813196 |  2 |   6
01672 | 9813197 |  3 |   6
01767 | 9794370 |  1 |   7
01767 | 9794374 |  2 |   7
01767 | 9794378 |  3 |   7
01767 | 9794382 |  4 |   7
01767 | 9794386 |  5 |   7
01767 | 9813181 |  1 |   8
01767 | 9813182 |  1 |   9

dbfiddle here

Related Solutions

Sql-server – Gaps and islands: client solution vs T-SQL query

And a 1 second solution...

;WITH cteSource(StartedAt, FinishedAt)
AS (
    SELECT      s.StartedAt,
            e.FinishedAt
    FROM        (
                SELECT  StartedAt,
                    ROW_NUMBER() OVER (ORDER BY StartedAt) AS rn
                FROM    dbo.Tasks
            ) AS s
    INNER JOIN  (
                SELECT  FinishedAt,
                    ROW_NUMBER() OVER (ORDER BY FinishedAt) + 1 AS rn
                FROM    dbo.Tasks
            ) AS e ON e.rn = s.rn
    WHERE       s.StartedAt > e.FinishedAt

    UNION ALL

    SELECT  MIN(StartedAt),
        MAX(FinishedAt)
    FROM    dbo.Tasks
), cteGrouped(theTime, grp)
AS (
    SELECT  u.theTime,
        (ROW_NUMBER() OVER (ORDER BY u.theTime) - 1) / 2
    FROM    cteSource AS s
    UNPIVOT (
            theTime
            FOR theColumn IN (s.StartedAt, s.FinishedAt)
        ) AS u
)
SELECT      MIN(theTime),
        MAX(theTime)
FROM        cteGrouped
GROUP BY    grp
ORDER BY    grp

Finding “islands” of holiday periods for employees

This is my attempt at solving this "groups and islands" problem, with comments in-line:

WITH GroupStart AS
(
    -- Identify the start of each new group
    SELECT 
        TD.id, TD.holidaydate, TD.holidaycomment,
            -- New group if the holidaycomment has changed
            -- (in holidaydate order)
            CASE WHEN holidaycomment =
                    LAG(TD.holidaycomment) OVER (
                        ORDER BY TD.holidaydate)
                THEN 0
                ELSE 1
            END AS gs
    FROM test_dates TD
),
Islands AS
(
    -- The island identifier is the number
    -- of new groups encountered so far
    SELECT 
        id, holidaydate, holidaycomment,  
        SUM(GS.gs) OVER (ORDER BY GS.holidaydate) AS grp
    FROM GroupStart GS
)
-- Results are now a pretty trivial aggregation
SELECT
    MIN(I.id) AS id -- Lowest id in group,
    MIN(I.holidaycomment) AS holidaycomment -- All the same per group,
    MIN(I.holidaydate) AS date_from -- First date in group,
    NULLIF(MAX(I.holidaydate), MIN(I.holidaydate)) AS date_to,
    1 + MAX(I.holidaydate) - MIN(I.holidaydate) AS days
FROM islands I
GROUP BY
    I.grp
ORDER BY
    I.grp,
    date_from;

I wrote this as my first Oracle query for about 20 years. The translation above is from my native SQL Server.

Related Question