How to find Max and Min dates in a specific interval?

Question

I'm having trouble finding the first and last date in a certain interval. If you look at the picture below you can see 3 different PersonIds. An interval is when the same Personid has a FromDate and ToDate that is coherent. Fromdate is 1 day after ToDate in the row above, if these 2 rows have the same PersonId. Row 1 is one Interval, and row 2-5 is one interval. What I'm trying to do is add 2 extra columns which represent the first date in an interval and the last date.

The code below is my attempt. First I have created a table with data. Then I'm creating a CTE, "NextDates", with extra columns with the value in FromDate on the row below, and one column with that row's PersonId. Like this:

Then I'm creating another CTE. "RowNumbers". Here I'm trying to mark every end of an interval, with either '1' or an increasing integer. This step might be totaly unnecessarily? If I can get the same "RowNr" in every row in that interval, then I should be able to group on that and find Max and Min date.

DROP TABLE IF EXISTS #ExampelTable
CREATE TABLE #ExampelTable (Id INT IDENTITY(1,1), PersonId INT, FromDate DATE, ToDate DATE)

INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2009-01-01','2009-06-30')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2010-01-26','2010-01-31')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2010-02-01','2010-06-20')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2010-06-21','2011-02-17')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2011-02-18','2011-07-31')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2013-12-03','2014-06-30')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2015-11-03','2016-06-30')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (20, '2020-11-03','2021-06-30')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (30, '2000-11-03','2000-11-25')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (30, '2000-11-26','2000-11-30')


/*Creating CTE with FromDate in next row, together with that rows PersonId*/

;WITH NextDates AS
(
    SELECT  id
        ,   PersonId
        ,   FromDate
        ,   ToDate
        ,   LEAD(FromDate) OVER (PARTITION BY PersonId ORDER BY PersonId, FromDate) AS NextDate
        ,   LEAD(PersonId) OVER (PARTITION BY PersonId ORDER BY PersonId, FromDate) AS NextPersonId
    FROM #ExampelTable
),

/*Creating flags on the rows where one interval end */
RowNumbers AS
(
    SELECT  id
        ,   1 AS IntervalEnd
        ,   ROW_NUMBER() OVER ( PARTITION BY PersonId ORDER BY FromDate ) AS RowNr
    FROM    NextDates
    WHERE   PersonId = NextPersonId
        AND NextDate <> DATEADD(dd,1,ToDate)
        OR  NextDate IS NULL
)


SELECT  NextDates.Id
    ,   NextDates.PersonId
    ,   NextDates.FromDate
    ,   NextDates.ToDate
    ,   NextDates.NextDate
    ,   RowNumbers.IntervalEnd
    ,   RowNumbers.RowNr
    ,   NULL AS MinDate
    ,   NULL AS MaxDate
FROM    NextDates
    LEFT JOIN RowNumbers ON NextDates.id = RowNumbers.Id

This is how it looks with the extra columns I'm created. The red lines show the end of every interval. And the blue ones show which value I want to have in the "MinDate" and "MaxDate" columns. Row 1 is simple, it's 1 interval, end then MinDate = FromDate and MaxDate = ToDate.
Rows 2-5 I need FromDate from row 2 in every row in that interval (Row 2-5) in the MinDate column. And ToDate on row 5 in every MaxDate row in that column.

score 4 · Accepted Answer · answered Apr 06 '23 at 14:53

4

I do not use SQL Server, but in this case syntax is very close to standard and this query works in SQL Server dbfiddle:

with grps as (
  select id, personid, fromdate, todate, 
          sum(grp) over (partition by personid order by fromdate) grp
  from (
    select et.*, 
           case when lag(todate) over (partition by personid order by fromdate) 
                   = dateadd(dd, -1, fromdate) 
                then 0 else 1 end grp
    from #ExampelTable et) a )
select id, personid, fromdate, todate, 
       min(fromdate) over (partition by personid, grp) d1, 
       max(todate) over (partition by personid, grp) d2 
  from grps

answered Apr 06 '23 at 14:53

Ponder Stibbons

14,723
2
21
24

Jost one remark: [Why is SELECT * considered ....](https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful). – Luuk Apr 06 '23 at 15:37
Wow thanks. That worked. I'm most impressed you actually understood my explanations in what I wanted to achieve! – Opston Apr 06 '23 at 17:05

How to find Max and Min dates in a specific interval?

1 Answers1