SQLTeam.com | Weblogs | Forums

Group by criteria and its repetition

An employee is assigned every day to a team to fullfill tasks. Usually you stick to the team for some days. I want to get a report of the timeline that marks the participation in the groups like this:

Team   from        to
A	2021-01-01	2021-01-02
B   2021-01-03	2021-01-05
C   2021-01-06	2021-01-07
B   2021-01-08	2021-01-10

If I group by Teams to get min (from) and max (to) it doesn't take into account that a period is defined by the first and the last time someone is continously assigned to that team.

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp

CREATE TABLE #temp (date DATETIME, Team nvarchar(1))
insert into #temp (date, Team) values ('1.1.2021', 'A')
insert into #temp (date, Team) values ('1.2.2021', 'A')
insert into #temp (date, Team) values ('1.3.2021', 'B')
insert into #temp (date, Team) values ('1.4.2021', 'B')
insert into #temp (date, Team) values ('1.5.2021', 'B')
insert into #temp (date, Team) values ('1.6.2021', 'C')
insert into #temp (date, Team) values ('1.7.2021', 'C')
insert into #temp (date, Team) values ('1.8.2021', 'B')
insert into #temp (date, Team) values ('1.9.2021', 'B')
insert into #temp (date, Team) values ('1.10.2021', 'B')

If I do:

select Team, min(date), max(date)
from #temp
group by Team

it just returns 3 rows as it does not look at the repetition. How do I achieve this with an older express version 10.5
Hope I did explain my point.
Thanks, Martin

This is an "islands" problem. Here's a typical islands solution.

;WITH cte_grp AS (
    SELECT t.*,
        ROW_NUMBER() OVER(PARTITION BY team ORDER BY date) -
        ROW_NUMBER() OVER(ORDER BY date) AS grp
    FROM #temp t
)
SELECT team, MIN(date) AS [from], MAX(date) AS [to]
FROM cte_grp
GROUP BY grp, team
ORDER BY [from], [to]

Thank you Scott. I had come across the "island problem" in the past, but I only remembered it after you had mentioned it's name. I do now understand to what it's name is related to (gaps & islands) and with this I hopefully will recognize this type the next time I bump into it.