从结果SQL查询中排除空值

我有一个查询，它返回结果，包含混合了空值的日期. 我的目标是通过合并值从所有列中删除空值.

注意:确保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

我不知道如何做到这一点.

下面是返回结果的数据集、表和SELECT.

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

对于完整的逻辑，让我们考虑一下这张图.蓝色矩形表示介于ds(日期开始)和de(日期结束)之间的日期范围.

因此，我必须返回介于@DateStart和@DateEnd之间的不相交和不相邻的范围(至少相差一天).在这种情况下，退货范围应为: (@DateStart，DE4)，(DS5-@DateEnd). 一百零二 还要注意，DateTo可以为空.在这种情况下，它意味着无穷大.