如何从日期列表中创建日期间隔

我没有要测试的 SQL Server 2005 实例，但这适用于 SQL Server 2008。它使用几个通用表表达式和一个窗口函数

--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    |