0

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

enter image description here

1 Answer 1

3

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    |
9
  • @McNets - thanks for the update. Feb 17, 2020 at 15:44
  • @McNets What causes a problem? CTE?
    – Akina
    Feb 17, 2020 at 15:45
  • @ScottHodgin Try to convert CTE to subquery.
    – Akina
    Feb 17, 2020 at 15:45
  • @Akina - Date is not a supported data type in 2005 Feb 17, 2020 at 15:45
  • 1
    @Akina and you need a single insert ...values for each line. Look at my edit
    – McNets
    Feb 17, 2020 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.