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):
1 Answer
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 |
-
-
-
-
-
1@Akina and you need a single
insert ...values
for each line. Look at my edit– McNetsFeb 17, 2020 at 15:46