注意，我 * 假设 * 最终预期结果的预期结果是错误的，因为它与其他2个结果不匹配。最后一个集合的预期结果中的最后一行和第一行的ID值都不是0，但是没有给出 * 为什么 * 它们这样做的解释。因此，我 * 假设 * 值应该是0，就像“中间”的行一样。
为此，我使用Tally来获取所需日期范围内的所有日期; Tally被限制为1，000行，比3年少一点，但是如果需要更多行，可以交叉连接到N。然后，我使用该计数创建一个内联日历表。接下来，我将该日历LEFT JOIN到您的数据中，并使用gaps和island方法将值放入组中。最后，我对这些组进行聚合，获取每个文件中的MIN和MAX日期：

USE Sandbox;
GO

CREATE TABLE dbo.YourTable (ID int,
                            FromDate date,
                            ToDate date);
INSERT INTO dbo.YourTable
VALUES(1,'20210505','20210510'),
      (2,'20210517','20210525');
GO

DECLARE @StartDate date = '20210501',
        @EndDate date = '20210528';

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT 0 AS I
    UNION ALL
    SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate))
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3), --1000 days
Dates AS(
    SELECT DATEADD(DAY, T.I, @StartDate) AS [Date],
           T.I
    FROM Tally T),
Grps AS(
    SELECT D.[Date],
           YT.ID,
           D.I - ROW_NUMBER() OVER (PARTITION BY ID ORDER BY D.[Date]) AS Grp
    FROM Dates D
         LEFT JOIN dbo.YourTable YT ON D.[Date] >= YT.FromDate AND D.[Date] <= YT.ToDate)
SELECT ISNULL(MAX(G.ID),0) AS ID,
       MIN(G.[Date]) AS FromDate,
       MAX(G.[Date]) AS ToDate
FROM Grps G
GROUP BY G.Grp
ORDER BY FromDate ASC;

GO
DROP TABLE dbo.YourTable;

db<>fiddle

您可以使用union all执行以下操作：

-- first get the existing rows
select id,
       (case when fromdate < @fromdate then @fromdate else fromdate end) as fromdate,
       (case when todate > @todate then @todate else todate end) as to_date
from t
where fromdate < @todate and
      todate > @fromdate
union all
-- then the in-between rows
select 0, dateadd(day, 1, todate) as fromdate, next_fromdate as todate
from (select t.*,
             dateadd(day, -1, lead(fromdate) over (order by fromdate)) as next_fromdate
      from t
     ) t
where fromdate >= @fromdate and todate <= @todate and
      next_todate is not null
union all
-- then the earliest record, if any
select 0, @fromdate, min(fromdate)
from t
where todate > @fromdate
having @fromdate < min(fromdate) 
union all
-- then the final record, if any
select 0, max(todate), @todate
from t
where fromdate < @todate
having @todate > max(todate);

Here是一个db〈〉小提琴。