我稍微修改了您的查询，并添加了rulesettingsmodified和newrotatesettingid。请检查一下这对你是否合适。

RuleSettingsModified AS
(
    SELECT Row_number() OVER (PARTITION BY rs.EmployeeID ORDER BY (SELECT NULL)) rn, rs.EmployeeID, rs.WeekOffFrequency
    FROM @RuleSettings rs   
)

(Id / (SELECT DISTINCT WeekOffFrequency FROM @RuleSettings WHERE EmployeeID = ed.EmployeeID))%(SELECT count(1) FROM RuleSettingsModified rsm WHERE rsm.EmployeeID = ed.EmployeeID)+1 AS NewRotateSettingId

;with dates_CTE (RosterDate) as (
    select @sdate 
    Union ALL
    select DATEADD(day, 1, RosterDate)
    from dates_CTE
    where RosterDate < @edate
),
EmployeeDates as (
SELECT  
ROW_NUMBER() over (partition by EmployeeId order by RosterDate) - 1 as Id,
EmployeeID,
RosterDate
FROM dates_CTE
CROSS APPLY (SELECT DISTINCT EmployeeId from  @RuleSettings)as cs
),
RuleSettingsModified AS
(
    SELECT Row_number() OVER (PARTITION BY rs.EmployeeID ORDER BY (SELECT NULL)) rn, rs.EmployeeID, rs.WeekOffFrequency
    FROM @RuleSettings rs   
)
,
EmployeeWeekOffFrequency as (

    SELECT *, 
    Id / (SELECT DISTINCT WeekOffFrequency from @RuleSettings where EmployeeID = ed.EmployeeID)+ 1  as WeekOffFrequency,
    (
        CASE 
        WHEN 
            ( 
                Id / (SELECT DISTINCT WeekOffFrequency FROM @RuleSettings WHERE EmployeeID = ed.EmployeeID)
            ) 
            < 
            (SELECT count(1) FROM @RuleSettings WHERE EmployeeID = ed.EmployeeID)
        THEN  
        (
            SELECT SettingId FROM @RuleSettings WHERE EmployeeID = ed.EmployeeID 
            AND SettingId = ((Id / (SELECT DISTINCT WeekOffFrequency FROM @RuleSettings  
            WHERE EmployeeID = ed.EmployeeID )) + 1 )
        ) 
        ELSE 99
        END
    ) as RotateSettingId,
    (Id / (SELECT DISTINCT WeekOffFrequency FROM @RuleSettings WHERE EmployeeID = ed.EmployeeID))%(SELECT count(1) FROM RuleSettingsModified rsm WHERE rsm.EmployeeID = ed.EmployeeID)+1 AS NewRotateSettingId
    from EmployeeDates as ed 
)
SELECT * FROM EmployeeWeekOffFrequency

请在这里查看完整的代码。

我实现了如下。在这里
expandrulesettings（add）：将rulesettings行展开为雇员的每个settingid的周数fffrequency。
numberofrotate（add）：获取rotate的数量（每个员工的所有settingid的数量）。
employeedates（change）：为每个员工分配轮换的id日期。
employeeweekofffrequency（change）：在匹配employeeid和id时加入employeedates和expandrulesettings表。

DECLARE @sdate date = '2020-09-10', @edate date = '2020-09-28'

;with dates_CTE (RosterDate) as (
    select @sdate 
    Union ALL
    select DATEADD(day, 1, RosterDate)
    from dates_CTE
    where RosterDate < @edate
),

-- Expand rows of RuleSettings to number of WeekOffFrequency each SettingId of employee.
ExpandRuleSettingsWork as (
  SELECT 0 AS IdForSettingId, SettingId, EmployeeID, ShiftPattern, WeekOffFrequency FROM @RuleSettings
  UNION ALL
  SELECT IdForSettingId+1, SettingId, EmployeeID, ShiftPattern, WeekOffFrequency FROM ExpandRuleSettingsWork
  WHERE IdForSettingId+1 < WeekOffFrequency
),
ExpandRuleSettings as (
  SELECT
    ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY SettingId,IdForSettingId)-1 AS Id,
    * FROM ExpandRuleSettingsWork
),
-- SELECT * FROM ExpandRuleSettings ORDER BY EmployeeID,SettingId,Id

-- Get number of rotate (number of all SettingId of each employee).
NumberOfRotate as (
  SELECT EmployeeID, COUNT(EmployeeID) AS Num FROM ExpandRuleSettings GROUP BY EmployeeID
),
-- SELECT * FROM NumberOfRotate

EmployeeDates as (
SELECT  
(ROW_NUMBER() over (partition by EmployeeId order by RosterDate) - 1) % Num as Id,
EmployeeID,
RosterDate
FROM dates_CTE
CROSS APPLY NumberOfRotate
)

--select * from EmployeeDates
--order by EmployeeID , RosterDate

,
EmployeeWeekOffFrequency as (
  SELECT ed.*, er.SettingId
  FROM EmployeeDates as ed INNER JOIN ExpandRuleSettings as er
  ON ed.EmployeeID=er.EmployeeID AND 
    ed.Id = er.Id
)

SELECT * FROM EmployeeWeekOffFrequency