T-SQL - Aggregate (MIN/MAX date) considering chronological sequence

I can not imagine how to fix my problem using t-sql.

I need MIN(ValidFrom) and MAX(ValidTo) out of a table with different time blocks considering a chronological sequence and a number.
A normal group won't work because MIN(ValidFrom) and MAX(ValidTo) for num 100 will not be correct.
I can not imagine how to get the expected results from this table using any analytic or rank function in t-sql.

I need minimum and maximum dates out of the following table.

ValidFrom	ValidTo	Num	ID Number
2019-06-01	2019-12-31	100	01234567
2020-01-01	2020-03-31	100	01234567
2020-04-01	2020-12-31	100	01234567
2021-01-01	2021-01-31	100	01234567
2021-02-01	2021-03-31	50	01234567
2021-04-01	2021-09-30	50	01234567
2021-10-01	2021-12-31	50	01234567
2022-01-01	2022-04-30	100	01234567

Expected result of the query:

ValidFrom	ValidTo	Num	ID Number
2019-06-01	2021-01-31	100	01234567
2021-02-01	2021-12-31	50	01234567
2022-01-01	2022-04-30	100	01234567

CodePudding user response：

A ranking can be calculated based on 2 ROW_NUMBER.
Once you have the ranking, it's simple to aggregate.

-- rank via ascending row_number   descending row_number
SELECT
  MIN(ValidFrom) AS ValidFrom
, MAX(ValidTo) AS ValidTo
, Num
, [ID Number]
-- , Rnk
FROM
(
    SELECT *
    , Rnk = ROW_NUMBER() OVER (PARTITION BY [ID Number] ORDER BY ValidFrom ASC, ValidTo ASC)
            ROW_NUMBER() OVER (PARTITION BY [ID Number], Num ORDER BY ValidFrom DESC, ValidTo DESC)
    FROM your_table
) q
GROUP BY [ID Number], Num, Rnk
ORDER BY [ID Number], MIN(ValidFrom)

ValidFrom	ValidTo	Num	ID Number
2019-06-01	2021-01-31	100	1234567
2021-02-01	2021-12-31	50	1234567
2022-01-01	2022-04-30	100	1234567

Second method

-- rank via summing flag on change
SELECT
  MIN(ValidFrom) AS ValidFrom
, MAX(ValidTo) AS ValidTo
, Num
, [ID Number]
, Rnk
FROM
(
  SELECT *
  , Rnk = SUM(flag) OVER (PARTITION BY [ID Number] ORDER BY ValidFrom, ValidTo)
  FROM
  (
    SELECT *
    , flag = IIF(Num = LAG(Num) OVER (PARTITION BY [ID Number] ORDER BY ValidFrom, ValidTo), 0, 1)
    FROM your_table
  ) q1
) q2
GROUP BY [ID Number], Num, Rnk
ORDER BY [ID Number], MIN(ValidFrom)

Demo on db<>fiddle here