如何在SQL Server中去除查询结果Null值并合并日期范围？

我有一个SQL查询返回的结果中包含混合Null值的日期列，目标是通过合并值去除所有列的Null值。已知DateFrom和DateTo列的有效日期数量相等。

当前查询返回结果 #

Client_Id   DateFrom         DateTo          
----------- ---------------- ----------------
          9             NULL             NULL
          9       2023-11-23             NULL
          9             NULL             NULL
          9             NULL       2023-12-22
          9             NULL             NULL
          9       2024-01-05             NULL
          9             NULL       2024-01-12
          9       2024-01-13       2024-02-03

期望结果 #

Client_Id   DateFrom         DateTo          
----------- ---------------- ----------------
          9       2023-11-23      2023-12-22
          9       2024-01-05      2024-01-12
          9       2024-01-13      2024-02-03

额外规则 #

需返回@DateBegin与@DateEnd之间不相交且非相邻（至少间隔1天）的日期范围，处理规则如下：

• 若原DateFrom小于@DateBegin则返回@DateBegin
• DateTo为Null或大于@DateEnd则返回@DateEnd
• DateTo为Null时表示无限期

数据集、表结构及当前查询语句 #

CREATE SCHEMA [TestDoc]
CREATE TABLE [TestDoc].[Contracts]
(
[Id] Int NOT NULL IDENTITY(1,1),
[Type_Id] int NOT NULL,
[Client_Id] int NOT NULL,
[DocNo] NVarChar(50) NOT NULL,
[DateFrom] Date NOT NULL,
[DateTo] Date NULL,

PRIMARY KEY CLUSTERED([Id])
)
GO

INSERT INTO TestDoc.Contracts
VALUES
(2, 9, '#dc4887311699','2023-11-08', null),
(2, 9, '#dc4887311699','2023-11-10', '2023-11-20'), --should not be selected
(2, 9, '#dc4887311699','2023-12-10', '2023-12-14'),
(2, 9, '#dc4887311699','2023-12-12', '2023-12-20'),
(2, 9, '#dc4887311699','2023-12-18', '2023-12-22'),
(2, 9, '#dc4887311699','2023-12-23', '2024-01-11'),
(2, 9, '#dc4887311699','2024-01-05', '2024-01-08'),
(2, 9, '#dc4887311699','2024-01-09', '2024-01-12'),
(2, 9, '#dc4887311699','2024-01-13', '2024-01-24')

declare 
  @Type_Id int = 2,
  @DateBegin date = '20231123',
  @DateEnd date = '20240203'

  select Client_Id, 

  DateFrom = case when lag(a.DateTo) over(partition by Client_Id order by DateTo) is null then @DateBegin 
    else
      case when datediff(day, lag(a.DateTo, 1, @DateBegin) over(partition by a.Client_Id order by a.DateTo), a.DateFrom) >= 1 then a.DateFrom end
    end,
    
  DateTo = case when lead(a.DateFrom) over(partition by Client_Id order by DateFrom) is null then @DateEnd
   else
      case when datediff(day, a.DateTo, lead(a.DateFrom, 1, @DateEnd) over(partition by a.Client_Id order by DateTo)) >= 1 then a.DateTo end
   end

  from 
  (
    select Client_Id,

    DateFrom = case when DateFrom < @DateBegin then @DateBegin else DateFrom end,
    DateTo = case when (DateTo > @DateEnd) or (DateTo is null) then @DateEnd else DateTo end

    from TestDoc.Contracts
    where (DateFrom < @DateEnd) and (isnull(DateTo, @DateEnd) > @DateBegin) and Type_Id = @Type_Id
  ) a
  order by a.Client_Id, a.DateFrom, a.DateTo

使用环境 #

数据库版本：Microsoft SQL Server 2022 16.0.4095.4

内容的提问来源于stack exchange，提问作者HEXHEXHEX