By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table Tbl (Name Char(1), [Type] Char(2), [Date] Date)
Insert Into Tbl (Name, [Type], [Date])
Values
('A','xx', '1/1/2018'),
('A','xx', '1/2/2018'),
('A','yy', '1/3/2018'),
('A','xx', '1/4/2018'),
('A','xx', '1/5/2018'),
('A','xx', '1/6/2018')
6 rows affected
With A As (
Select Name, [Type], [Date],
Row_Number() Over (Order by [Date]) As Num,
Row_Number() Over (Partition by Name, [Type] Order by [Date]) As Num_1
From Tbl)
Select Name, [Type], Min([Date]) As StartDate, Max([Date]) As EndDate
From A
Group by Name, [Type], Num - Num_1
Order by StartDate
| Name | Type | StartDate | EndDate |
|---|---|---|---|
| A | xx | 2018-01-01 | 2018-01-02 |
| A | yy | 2018-01-03 | 2018-01-03 |
| A | xx | 2018-01-04 | 2018-01-06 |