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

user_18028150 0
Viewed 91

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
gaps-and-islands sql-server-2012 tsql sql-server sql
1 Answers

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

user_40034190
Related