SQL Server min max with intermediate record

Question

I am sorry if this question has been answered before, but I cannot find it. Perhaps of my bad keywords.

I have this table:

CREATE TABLE test1 
(
    Employee VARCHAR(10),
    Band VARCHAR(10),
    StartDate DATE,
    EndDate DATE
)

INSERT INTO test1 
VALUES ('Emp1', 'Band1', '2009-01-01', '2010-12-31'),
       ('Emp1', 'Band1', '2011-01-01', '2012-12-31'), 
       ('Emp1', 'Band1', '2013-01-01', '2013-08-31'),
       ('Emp1', 'Band2', '2013-09-01', '2013-12-31'),
       ('Emp1', 'Band2', '2014-01-01', '2014-06-30'),
       ('Emp1', 'Band1', '2014-07-01', '2014-12-31'),
       ('Emp1', 'Band1', '2015-01-01', '2018-08-31'),
       ('Emp2', 'Band1', '2012-01-01', '2014-12-31'),
       ('Emp2', 'Band1', '2015-01-01', '2018-03-31')

Results in this table:

Employee   Band   StartDate   EndDate
----------------------------------------
Emp1       Band1  2009-01-01  2010-12-31
Emp1       Band1  2011-01-01  2012-12-31
Emp1       Band1  2013-01-01  2013-08-31
Emp1       Band2  2013-09-01  2013-12-31
Emp1       Band2  2014-01-01  2014-06-30
Emp1       Band1  2014-07-01  2014-12-31
Emp1       Band1  2015-01-01  2018-08-31
Emp2       Band1  2012-01-01  2014-12-31
Emp2       Band1  2015-01-01  2018-03-31

What I want to create is a result table grouping each employee with the band and the minimum start date and maximum end date, but when an intermediate record (band) exist in the middle between the similar band, the end date should be capped and the start date of the next group in similar band should reset again.

Employee   Band   StartDate   EndDate
----------------------------------------
Emp1       Band1  2009-01-01  2013-08-31
Emp1       Band2  2013-09-01  2014-06-30
Emp1       Band1  2014-07-01  2018-08-31
Emp2       Band1  2012-01-01  2018-03-31

I have tried CTE to get the max and min of each band and compare it with the original table, but I still fail. I also tried using lead and lag but still fail.

Nice to have

Supposed the second record's EndDate is 2012-02-01, I prefer the result is still one record for the first Band1 group.

Employee   Band   StartDate   EndDate
----------------------------------------
Emp1       Band1  2009-01-01  2013-08-31

With each row of the result only differing in Band, I would able to figure out how long an employee has stayed in a certain band before moving to other band (different band).

But it is something nice to have.

Answer 1

Apparently you want to group rows whenever an employee changes band. This is straight forward with window functions. The following solution adds a "change" flag to rows whenever the band changes. Be advised that it ignores gaps. Add DATEDIFF check to the case statement to find the actual amount of time the person was associated with a band:

DECLARE @test1 TABLE(
    Employee VARCHAR(10),
    Band VARCHAR(10),
    StartDate DATE,
    EndDate DATE
);

INSERT INTO @test1 VALUES
('Emp1', 'Band1', '2009-01-01', '2010-12-31'),
('Emp1', 'Band1', '2011-01-01', '2012-12-31'),
('Emp1', 'Band1', '2013-01-01', '2013-08-31'),
('Emp1', 'Band2', '2013-09-01', '2013-12-31'),
('Emp1', 'Band2', '2014-01-01', '2014-06-30'),
('Emp1', 'Band1', '2014-07-01', '2014-12-31'),
('Emp1', 'Band1', '2015-01-01', '2018-08-31'),
('Emp2', 'Band1', '2012-01-01', '2014-12-31'),
('Emp2', 'Band1', '2015-01-01', '2018-03-31');

WITH cte1 AS (
    SELECT *,
        CASE WHEN LAG(Band) OVER (PARTITION BY Employee ORDER BY StartDate) = Band /* AND DATEDIFF(...) */ THEN 0 ELSE 1 END AS Chg
    FROM @test1
), cte2 AS (
    SELECT *,
        SUM(Chg) OVER (PARTITION BY Employee ORDER BY StartDate) AS Grp
    FROM cte1
)
SELECT Employee, Band, MIN(StartDate), Max(EndDate)
FROM cte2
GROUP BY Employee, Band, Grp

DB Fiddle

This is the intermediate result in case you want to see how it works:

| Employee | Band  | StartDate           | EndDate             | Chg | Grp |
|----------|-------|---------------------|---------------------|-----|-----|
| Emp1     | Band1 | 01/01/2009 00:00:00 | 31/12/2010 00:00:00 | 1   | 1   |
| Emp1     | Band1 | 01/01/2011 00:00:00 | 31/12/2012 00:00:00 | 0   | 1   |
| Emp1     | Band1 | 01/01/2013 00:00:00 | 31/08/2013 00:00:00 | 0   | 1   |
| Emp1     | Band2 | 01/09/2013 00:00:00 | 31/12/2013 00:00:00 | 1   | 2   |
| Emp1     | Band2 | 01/01/2014 00:00:00 | 30/06/2014 00:00:00 | 0   | 2   |
| Emp1     | Band1 | 01/07/2014 00:00:00 | 31/12/2014 00:00:00 | 1   | 3   |
| Emp1     | Band1 | 01/01/2015 00:00:00 | 31/08/2018 00:00:00 | 0   | 3   |
| Emp2     | Band1 | 01/01/2012 00:00:00 | 31/12/2014 00:00:00 | 1   | 1   |
| Emp2     | Band1 | 01/01/2015 00:00:00 | 31/03/2018 00:00:00 | 0   | 1   |

Answer 2

This is commonly known as Gaps and Islands.

One approach

Example

Declare @YourTable Table ([Employee] varchar(50),[Band] varchar(50),[Start] date,[End] date)
Insert Into @YourTable Values 
 ('Emp1','Band1','2009-01-01','2010-12-31')
,('Emp1','Band1','2011-01-01','2012-12-31')
,('Emp1','Band1','2013-01-01','2013-08-31')
,('Emp1','Band2','2013-09-01','2013-12-31')
,('Emp1','Band2','2014-01-01','2014-06-30')
,('Emp1','Band1','2014-07-01','2014-12-31')
,('Emp1','Band1','2015-01-01','2018-08-31')
,('Emp2','Band3','2012-01-01','2014-12-31')
,('Emp2','Band3','2015-01-01','2018-03-31')

;with cte as (
Select *,Grp = sum(Flg) over (Partition By Employee Order by [End])
 From (
        Select *,Flg = IsNull(datediff(DAY,Lag([End],1) over (Partition By Employee,Band Order by [End]) ,[Start]) - 1,1)
         From  @YourTable
      ) A
)
Select Employee
      ,Band
      ,[Start] = min([Start])
      ,[End]   = max([End])
 From cte
 Group By Employee,Band,Grp
 Order by Employee,max([End])

Returns

Employee    Band    Start       End
Emp1        Band1   2009-01-01  2013-08-31
Emp1        Band2   2013-09-01  2014-06-30
Emp1        Band1   2014-07-01  2018-08-31
Emp2        Band3   2012-01-01  2018-03-31

If it helps with the visualization, the CTE produces the following

Notice the Flag and Group Columns