使用递归cte：

with cte as (
      select convert(date, v.dte) as dte, 1 as lev
      from (values ('2000-01-01'), ('2001-01-01'), ('2002-01-01'), ('2003-01-01'), ('2004-01-01')
           ) v(dte)
      union all
      select dateadd(day, 7, dte), lev + 1
      from cte
      where lev < 5
     )
select dte
from cte;

这是一把小提琴。

天哪，伙计们。。。不要使用rcte（递归cte）来处理这些事情（任何带有增量的事情）。即使是很小的行数，对于资源使用和较差的性能来说，它们也是非常令人讨厌的。一个写得好的while循环将击败它。
有一些方法可以关闭rcte、循环和其他形式的rbar。这篇文章太长了，不能在这里重复，但这里有一篇文章介绍了一些性能和资源使用度量。
隐藏rbar：递归cte计数
首先，创建这个有用的函数
首先，我们需要一个叫做“t-sql的瑞士军刀”的工具的帮助。它是一个itvf（内联表值函数）形式的“计数表”，它是以伊兹克-本-甘首先规定和描述的方式构建的。下面是函数的代码（fntally）。文档比代码长得多。

CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
 Purpose:
 Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 10 Quadrillion.

 Usage:
--===== Syntax example
 SELECT t.N
   FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;
 @ZeroOrOne will internally conver to a 1 for any number other than 0 and a 0 for a 0.
 @MaxN has an operational domain from 0 to 4,294,967,296. Silent truncation occurs for larger numbers.

 Please see the following notes for other important information

 Notes:
 1. This code works for SQL Server 2008 and up.
 2. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
    Refer to the following URL for how it works.
    https://www.itprotoday.com/sql-server/virtual-auxiliary-table-numbers
 3. To start a sequence at 0, @ZeroOrOne must be 0. Any other value that's convertable to the BIT data-type
    will cause the sequence to start at 1.
 4. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
 5. If @MaxN is negative or NULL, a "TOP" error will be returned.
 6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 4,294,967,296. If a larger
    number is used, the function will silently truncate after that max. If you actually need a sequence with that many
    or more values, you should consider using a different tool. ;-)
 7. There will be a substantial reduction in performance if "N" is sorted in descending order.  If a descending sort is
    required, use code similar to the following. Performance will decrease by about 27% but it's still very fast 
    especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
    If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.

    DECLARE @MaxN BIGINT; 
     SELECT @MaxN = 1000;
     SELECT DescendingN = @MaxN-N+1 
       FROM dbo.fnTally(1,@MaxN);

 8. There is no performance penalty for sorting "N" in ascending order because the output is implicity sorted by
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
 9. This will return 1-10,000,000 to a bit-bucket variable in about 986ms.
    This will return 0-10,000,000 to a bit-bucket variable in about 1091ms.
    This will return 1-4,294,967,296 to a bit-bucket variable in about 9:12( mi:ss).

 Revision History:
 Rev 00 - Unknown     - Jeff Moden 
        - Initial creation with error handling for @MaxN.
 Rev 01 - 09 Feb 2013 - Jeff Moden 
        - Modified to start at 0 or 1.
 Rev 02 - 16 May 2013 - Jeff Moden 
        - Removed error handling for @MaxN because of exceptional cases.
 Rev 03 - 07 Sep 2013 - Jeff Moden 
        - Change the max for @MaxN from 10 Billion to 10 Quadrillion to support an experiment. 
          This will also make it much more difficult for someone to actually get silent truncation in the future.
 Rev 04 - 04 Aug 2019 - Jeff Moden
        - Enhance performance by making the first CTE provide 256 values instead of 10, which limits the number of
          CrossJoins to just 2. Notice that this changes the maximum range of values to "just" 4,294,967,296, which
          is the entire range for INT and just happens to be an even power of 256. Because of the use of the VALUES
          clause, this code is "only" compatible with SQLServer 2008 and above.
        - Update old link from "SQLMag" to "ITPro". Same famous original article, just a different link because they
          changed the name of the company (twice, actually).
        - Update the flower box notes with the other changes.

**********************************************************************************************************************/

        (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  H2(N) AS ( SELECT 1 
               FROM (VALUES
                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    )V(N))            --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
            SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
            SELECT TOP(@MaxN)
                   N = ROW_NUMBER() OVER (ORDER BY N)
              FROM H8
;
GO

创建10000行测试数据
现在，让我们创建一点测试数据。。。只有一万次约会。除了日期（为了让它有趣），我还添加了一个以周为单位的合同长度列。

--===== Create the test data
   DROP TABLE IF EXISTS #TestTable
;
 SELECT SomeDate = CONVERT(DATE,DATEADD(dd,ABS(CHECKSUM(NEWID())%DATEDIFF(dd,'2000','2021')),'2000'))
        ,ContractWeeks = ABS(CHECKSUM(NEWID())%53)*5
   INTO #TestTable
   FROM dbo.fnTally(1,10000)
;

使用该功能使其简单快捷
在那之后，这个问题的代码就简单多了（我添加了一些列只是为了演示的目的。。。去掉你不想要或不需要的东西）。

SELECT  SomeDate
        ,Week#             = wk.N+1
        ,WeekStartDate     = DATEADD(wk,wk.N  ,tt.SomeDate)
        ,NextWeekStartDate = DATEADD(wk,wk.N+1,tt.SomeDate)
   INTO #Results
   FROM #TestTable tt
  CROSS APPLY dbo.fnTally(0,ContractWeeks) wk
;

演出
以下是性能数据。。。

Table 'Worktable'. Scan count 52, logical reads 33433, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TestTable__________________________________________________________________________________________________________0000000001E8'. 
Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 562 ms,  elapsed time = 576 ms.

(1320535 rows affected)

rcte示例
下面是rcte的一个版本，它将开始日期乘以合同周数，然后将其转储到一个表中。

with cte as (
      select SomeDate as dte, 1 as lev
            ,ContractWeeks
      from #TestTable
      union all
      select dateadd(day, 7, dte), lev + 1
            ,ContractWeeks
      from cte
      where lev <= ContractWeeks
     )
select dte
into #Results1                            
from cte
order by 1
OPTION (MAXRECURSION 0)
;

性能和这是从运行的统计数据。。。比较逻辑读取、cpu和持续时间。这仅仅是10000行的输入和130万行的输出。。。和fntally方法一样。。。

Table 'Worktable'. Scan count 2, logical reads 7903617, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TestTable__________________________________________________________________________________________________________0000000001E8'.
Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 6391 ms,  elapsed time = 6388 ms.

(1320535 rows affected

这里的底线是rcte
使用了约235次或23500%的读取次数
cpu使用量增加约10倍或1000%
花费了大约10倍或1000%的时间
对rcte的增量（计数）说“不”。

好的，各位。专栏在评论我之前的文章时说。。。
不过，尽管我很想使用这个解决方案，但遗憾的是我只能以只读方式访问服务器，因此无法创建临时表或自定义函数，因此rcte选项最适合我们。
不。。。即使在这种情况下，rcte也不适合你。
首先，使用temp表只是为了构建测试数据，因为我没有访问op发布的原始表的权限。它们只是为了测试。。。他们不是解决方案的一部分。以这种方式创建测试数据在受访者中是一种非常常见的做法，您只需认识到这一事实，并用实际表的实际名称替换临时表的名称。
专栏还写了以下内容。。。
但是，我们的服务器一直存在性能问题，所以我很高兴您给了我这个更好的替代解决方案，希望有一天我们真的需要优化时可以使用它。
我的看法是。。。为什么要等？从现在开始。将fntally函数的代码提交给dba，说明为什么它应该是每个数据库的一部分，或者至少包含在可访问的实用程序数据库中。如果dba真的很在行，他可能已经有了类似的东西（希望不是基于rcte或while循环），你只需要问一下就可以了。
如果这是不可能的，那么这个问题仍然可以做同样的效果（高性能，短代码，正确的答案），但是，由于你不能做“想在盒子外面”的事情，你必须花一些时间来“想在盒子里面”。
既然如此，而且您需要将temp表的名称更改为一个真实的表（我使用了上一个回复中的temp表来测试它），下面稍微长一点的代码使用类似的ccte（cascading cte）代码来替换无法生成的fntally函数。

WITH 
     H1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))H0(N)) --16^1 or 16 rows
,    H8(N) AS (SELECT 1 FROM H1 a, H1 b, H1 c, H1 d, H1 e, H1 f, H1 g, H1 h) --16^8 or 4,294,967,296 rows
, Tally(N) AS (SELECT N = ROW_NUMBER() OVER (ORDER BY N) - 1 FROM H8)
    SELECT  SomeDate
           ,Week#             = wk.N+1
           ,WeekStartDate     = DATEADD(wk,wk.N  ,tt.SomeDate)
           ,NextWeekStartDate = DATEADD(wk,wk.N+1,tt.SomeDate)
      FROM #TestTable tt
     CROSS APPLY (SELECT TOP (tt.ContractWeeks+1) N FROM Tally) wk
;

底线是，这是很容易避免恐怖的rcte产生和增量计数，即使是这样的东西。你只需要教会自己如何在你所局限的盒子里思考d