Sql 如何找到特定时间间隔内的最大和最小日期

在一定的时间间隔内，我很难找到第一次和最后一次约会.如果你看下面的图片，你可以看到3个不同的个人ID.间隔是指相同的PersonID具有连贯的FromDate和ToDate.如果以上行中的这两行具有相同的PersonID，则FromDate是ToDate之后的1天.行1是一个区间，行2-5是一个区间.我想要做的是添加两个额外的列，它们代表一个间隔中的第一个日期和最后一个日期.

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:

然后我要创建另一个CTE."行数".在这里，我试图用‘1’或一个递增的整数来标记时间间隔的每一端.这一步可能是完全不必要的？如果我可以在该间隔的每一行中获得相同的"RowNr"，那么我应该能够对其进行分组，并找到最大和最小日期.

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.