How to make date intervals from a list of date

Question

How to make date intervals from a list of date (Group by columns A and C, the date range consists of a continuous date sequence):

Answer 1

I don't have a SQL Server 2005 instance to test against, but this works under SQL Server 2008. It uses a couple of Common Table Expressions and a Windowing Function

--demo setup
DECLARE @TestData TABLE (
    ColA varchar(10)
    ,ColB datetime
    ,ColC char(1)
    )

insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-02','T');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-03' ,'T');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-04' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-06' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-07' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-08' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-02-07' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-02-08' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-02-09' ,'T');
insert into @TestData (ColA,ColB,ColC) values ('Dog','2019-03-09' ,'T');
;
--solution
WITH mycte1
AS (
    SELECT *
        ,DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY ColA,ColC ORDER BY [ColB]
                ), [ColB]) AS grp
    FROM @TestData
    )
,mycte2 as
(
SELECT ColA
    ,min([ColB]) AS [Start]
    ,max([ColB]) AS [End]
    ,ColC
FROM mycte1
GROUP BY ColA,ColC
    ,grp
)
select * from mycte2
ORDER BY ColA, Start

---

| ColA | Start      | End        | ColC |
|------|------------|------------|------|
| Cat  | 2019-01-02 | 2019-01-03 | T    |
| Cat  | 2019-01-04 | 2019-01-04 | G    |
| Cat  | 2019-01-06 | 2019-01-08 | G    |
| Cat  | 2019-02-07 | 2019-02-08 | G    |
| Cat  | 2019-02-09 | 2019-02-09 | T    |
| Dog  | 2019-03-09 | 2019-03-09 | T    |