How to calculate a rotating week schedule with actual dates and times within a date range in a single query

From a series of questions (above and below), in the end, I asume this is the core question: Given your database structure and information, how to calculate the date appointment?

In my understand from reading the diagram, Period has the start and end date of a series of appointment. WeekRotations give you the order of the week in the periods (SequenceNr). But note that this is cylcical (see discussion below). So if the cycle repeating every 3 weeks in a period of 50 weeks, you only get 3 records. DayOfWeek gives you the day in that week. (0=Monday, 1=Tuesday,...) For my SQL-Server collation, it is 1= Sunday, 2=Monday,..., 7=Saturday. If you use my query, please check your collation is consistent with it (for the purpose of system function).

Challenge 1: Get the Monday of the week of StartDate. (I do not use the user created function because they can cause problems with performance, depending on how they define. I prefer system function).

DATEADD( day, CASE DATEPART(weekday, Periods.DateStart) WHEN 1 THEN -6 ELSE 2 - DATEPART(weekday, Periods.DateStart)  END , Periods.DateStart )

Challenge 2: Get the metadata of rotation

SELECT
Periods.ID PeriodId
, MAX ( DATEDIFF (week, Periods.StartDate, Periods.EndDate ) + 1 ) NumberOfWeek
, COUNT(WeekRotations.ID) LengthOfCycle
FROM Periods
  INNER JOIN WeekRotations ON Periods.ID=WeekRotations.PeriodID
WHERE Periods.DateStart <= '2021-12-31' OR Periods.EndDate >= '2021-01-31'  -- cross year periods
GROUP BY Periods.ID = Periods.ID
) RotationMetadata

Challenge 3: Get the right appointment date

DATEADD ( day , Appointments.DayOfWeek, 
  DATEADD ( week, t.num * LengthOfCycle + WeekRotations.SequenceNr - 1, -- getting to the right week
              DATEADD( day, CASE DATEPART(weekday, Periods.DateStart) WHEN 1 THEN -6 ELSE 2 - DATEPART(weekday, Periods.DateStart)  END , Periods.DateStart )
  )
) AppointmentDate

So, the final query is below. It would be helpful if you have a TallyTable in your database

;WITH d AS (
SELECT digits
FROM ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) d(Digits)
), TallyTable AS (
SELECT d1.digits *10 + d2.digits num
FROM d d1
  CROSS JOIN d d2
WHERE d1.digits *10 + d2.digits < 60  -- get a large enough number to satisfy all use case. 
)
SELECT   tbl.*, Customers.*
FROM 
(
SELECT 
    DATEADD ( day , Appointments.DayOfWeek, 
      DATEADD ( week, t.num * LengthOfCycle + WeekRotations.SequenceNr - 1,
                  DATEADD( day, CASE DATEPART(weekday, Periods.DateStart) WHEN 1 THEN -6 ELSE 2 - DATEPART(weekday, Periods.DateStart)  END , Periods.DateStart )
      )
    ) AppointmentDate
    , Periods.DateStart, Periods.DateEnd, Periods.Id PeriodId, Periods.CustomerId
    , WeekRotations.Id WeekRotationID, WeekRotations.SequenceNr
    , Appointments.DayOfWeek, Appointments.TimeStart, Appointments.TimeEnd
FROM 
    Periods                      
    INNER JOIN WeekRotations ON Periods.ID                  = WeekRotations.PeriodID 
    LEFT OUTER JOIN Appointments  ON Appointments.WeekRotationID = WeekRotations.ID  
    LEFT OUTER JOIN (
        SELECT
        p.ID PeriodId
        , MAX ( DATEDIFF (week, p.DateStart, p.DateEnd ) + 1 ) NumberOfWeek
        , COUNT(WeekRotations.ID) LengthOfCycle
        FROM Periods p
          INNER JOIN WeekRotations ON p.ID=WeekRotations.PeriodID
        WHERE p.DateStart <= '2021-12-31' OR p.DateEnd >= '2021-01-01'  -- cross year periods
        GROUP BY p.ID 
        ) RotationMetadata
        ON RotationMetadata.PeriodId = Periods.ID
    OUTER APPLY (
        SELECT num FROM TallyTable WHERE num <= (RotationMetadata.NumberOfWeek / RotationMetadata.LengthOfCycle + 1 )
    ) t
WHERE
    Periods.DateStart <= '2021-12-31' OR Periods.DateEnd >= '2021-01-01' -- cross year periods
) tbl
LEFT OUTER JOIN Customers ON tbl.CustomerId = Customers.Id
WHERE tbl.AppointmentDate BETWEEN '2021-01-01' AND '2021-12-31' 
AND tbl.AppointmentDate BETWEEN tbl.DateStart AND tbl.DateEnd

I would suggest to create a dedicated table to store all the date of the years you planing to cover, Lets say from 2020-01-01 to 2050-12-31. The column structure of this tables is simple:

create table dbo.DateTable ([Date] date primary key, DayOfWeek tinyint, WeekNumber int)

Where the WeekNumber whould be the number of week between the tables first entry, and the current entry. Can easily be obtained by update dt set WeekNumber = datediff(week, @vFirstDate, dt.Date) from dbo.DateTable dt, where the @vFirstDate is the min of Date from the table.

To get the actual dates for a Period, you only need to join the DateTabe 2 times:

• First on the Period.DateStart = Date condition, let's call it fdt
• Then on the Date between Period.DateStart and Period.DateEnd, let's call id dt

If the WeekNumber difference is dividable by the SequenceNr from WeekRotation you got the correct Date.

To further narrow the DateTable down, you can join the Appointments table too on the condition of dt.DayOfWeek = Appointments.DayOfWeek

The DateTable only need's to be populated once, and extended once a year, to increase it with the next year.

Modification on 14-Mar-2021 (Calendar dates stored in a table): Query to store calendar dates in a table named Calendar:

 ;WITH seq(n) AS 
 (
   SELECT 0 UNION ALL SELECT n + 1 FROM seq
   WHERE n < DATEDIFF(DAY, '1 jan 2021', '31 dec 2021')
 ),
 d(d) AS 
 (
   SELECT DATEADD(DAY, n, '1 jan 2021') FROM seq
 ),
 calendar as(
 SELECT cast(d as date) Calendar_Date ,datepart(WK,D)  Week,DATEPART(WEEKDAY,D)-2 WeekDay,ROW_NUMBER()over(partition by DATEPART(WEEKDAY,D) order by d)WekDaySequence FROM d 
 )
 select * into Calendar from Calendar
 OPTION (MAXRECURSION 0)

Query#1 (Taking time part from TimeStart and TimeEnd):

 select  c.Calendar_date,WeekRotationID,periodid,AppointmentsID,dayofweek,timestart,timeend,maxSequenceNR,period_startdate ,period_enddate ,customerid,custome_rname from calendar c
 inner join (SELECT     wr.id WeekRotationID, wr.sequencenr,wr.periodid, a.id AppointmentsID, a.dayofweek ,CONVERT(VARCHAR(5), timestart, 108)timestart,CONVERT(VARCHAR(5), timeend, 108)timeend,wr.maxSequenceNr,p.datestart period_startdate,p.dateend period_enddate,p.customerid,cust.name custome_rname
 FROM     
     (select id,periodid,sequencenr,max(sequencenr)over(partition by periodid)maxSequenceNR from WeekRotations) wr
    INNER JOIN Appointments a ON a.WeekRotationID = wr.ID  
    inner join periods p on wr.periodid=p.id 
    inner join customers cust on p.customerid=cust.id)t
    
    on c.WekDaySequence% t.maxSequenceNr=0  and t.dayofweek=c.WeekDay
    and c.calendar_date between t.period_startdate and period_enddate
 
 order by Calendar_Date

Query#2 (Taking time part from TimeStart and TimeEnd and concatenating with Date):

 select  c.Calendar_date,WeekRotationID,periodid,AppointmentsID,dayofweek,concat(Calendar_Date ,' ',  timestart)timestart,concat(Calendar_Date ,' ',  timeend)timeend,maxSequenceNR,period_startdate ,period_enddate ,customerid,custome_rname from calendar c
 inner join (SELECT     wr.id WeekRotationID, wr.sequencenr,wr.periodid, a.id AppointmentsID, a.dayofweek ,CONVERT(VARCHAR(5), timestart, 108)timestart,CONVERT(VARCHAR(5), timeend, 108)timeend,wr.maxSequenceNr,p.datestart period_startdate,p.dateend period_enddate,p.customerid,cust.name custome_rname
 FROM     
     (select id,periodid,sequencenr,max(sequencenr)over(partition by periodid)maxSequenceNR from WeekRotations) wr
    INNER JOIN Appointments a ON a.WeekRotationID = wr.ID  
    inner join periods p on wr.periodid=p.id 
    inner join customers cust on p.customerid=cust.id)t
    
    on c.WekDaySequence% t.maxSequenceNr=0  and t.dayofweek=c.WeekDay
    and c.calendar_date between t.period_startdate and period_enddate
 
 order by Calendar_Date
 

db<>fiddle here

**I have taken only time part from appointment start and end time.

One appointment each monday for Q1 2021:

 ;WITH seq(n) AS 
 (
   SELECT 0 UNION ALL SELECT n + 1 FROM seq
   WHERE n < DATEDIFF(DAY, '1 jan 2021', '31 dec 2021')
 ),
 d(d) AS 
 (
   SELECT DATEADD(DAY, n, '1 jan 2021') FROM seq
 ),
 calendar as(
 SELECT d Calendar_Date ,datepart(WK,D)  Week,DATEPART(WEEKDAY,D)-2 WeekDay,ROW_NUMBER()over(partition by DATEPART(WEEKDAY,D) order by d)WekDaySequence FROM d 
 )
 
 select  c.Calendar_date,WeekRotationID,periodid,AppointmentsID,dayofweek,timestart,timeend,maxSequenceNR,period_startdate,period_enddate,customerid,custome_rname from calendar c
 inner join (SELECT     wr.id WeekRotationID, wr.sequencenr,wr.periodid, a.id AppointmentsID, a.dayofweek ,cast( a.timestart as time)timestart,cast(a.timeend as time)timeend,wr.maxSequenceNr,p.datestart period_startdate,p.dateend period_enddate,p.customerid,cust.name custome_rname
 FROM     
     (select id,periodid,sequencenr,max(sequencenr)over(partition by periodid)maxSequenceNR from WeekRotations) wr
    INNER JOIN Appointments a ON a.WeekRotationID = wr.ID  
    inner join periods p on wr.periodid=p.id 
    inner join customers cust on p.customerid=cust.id)t
    
    on c.WekDaySequence% t.maxSequenceNr=0  and t.dayofweek=c.WeekDay
    and c.calendar_date between t.period_startdate and period_enddate
 
 order by Calendar_Date
 OPTION (MAXRECURSION 0)

One appointment every other monday for Q1 2021:

 ;WITH seq(n) AS 
 (
   SELECT 0 UNION ALL SELECT n + 1 FROM seq
   WHERE n < DATEDIFF(DAY, '1 jan 2021', '31 dec 2021')
 ),
 d(d) AS 
 (
   SELECT DATEADD(DAY, n, '1 jan 2021') FROM seq
 ),
 calendar as(
 SELECT d Calendar_Date ,datepart(WK,D)  Week,DATEPART(WEEKDAY,D)-2 WeekDay,ROW_NUMBER()over(partition by DATEPART(WEEKDAY,D) order by d)WekDaySequence FROM d 
 )
 
 select  c.Calendar_date,WeekRotationID,periodid,AppointmentsID,dayofweek,timestart,timeend,maxSequenceNR,period_startdate,period_enddate,customerid,custome_rname from calendar c
 inner join (SELECT     wr.id WeekRotationID, wr.sequencenr,wr.periodid, a.id AppointmentsID, a.dayofweek ,cast( a.timestart as time)timestart,cast(a.timeend as time)timeend,wr.maxSequenceNr,p.datestart period_startdate,p.dateend period_enddate,p.customerid,cust.name custome_rname
 FROM     
     (select id,periodid,sequencenr,max(sequencenr)over(partition by periodid)maxSequenceNR from WeekRotations) wr
    INNER JOIN Appointments a ON a.WeekRotationID = wr.ID  
    inner join periods p on wr.periodid=p.id 
    inner join customers cust on p.customerid=cust.id)t
    
    on c.WekDaySequence% t.maxSequenceNr=0  and t.dayofweek=c.WeekDay
    and c.calendar_date between t.period_startdate and period_enddate
 
 order by Calendar_Date
 OPTION (MAXRECURSION 0)

One appointment every 3 weeks on monday for Q1 2021:

 ;WITH seq(n) AS 
 (
   SELECT 0 UNION ALL SELECT n + 1 FROM seq
   WHERE n < DATEDIFF(DAY, '1 jan 2021', '31 dec 2021')
 ),
 d(d) AS 
 (
   SELECT DATEADD(DAY, n, '1 jan 2021') FROM seq
 ),
 calendar as(
 SELECT d Calendar_Date ,datepart(WK,D)  Week,DATEPART(WEEKDAY,D)-2 WeekDay,ROW_NUMBER()over(partition by DATEPART(WEEKDAY,D) order by d)WekDaySequence FROM d 
 )
 
 select  c.Calendar_date,WeekRotationID,periodid,AppointmentsID,dayofweek,timestart,timeend,maxSequenceNR,period_startdate ,period_enddate ,customerid,custome_rname from calendar c
 inner join (SELECT     wr.id WeekRotationID, wr.sequencenr,wr.periodid, a.id AppointmentsID, a.dayofweek ,cast( a.timestart as time)timestart,cast(a.timeend as time)timeend,wr.maxSequenceNr,p.datestart period_startdate,p.dateend period_enddate,p.customerid,cust.name custome_rname
 FROM     
     (select id,periodid,sequencenr,max(sequencenr)over(partition by periodid)maxSequenceNR from WeekRotations) wr
    INNER JOIN Appointments a ON a.WeekRotationID = wr.ID  
    inner join periods p on wr.periodid=p.id 
    inner join customers cust on p.customerid=cust.id)t
    
    on c.WekDaySequence% t.maxSequenceNr=0  and t.dayofweek=c.WeekDay
    and c.calendar_date between t.period_startdate and period_enddate
 
 order by Calendar_Date
 OPTION (MAXRECURSION 0)

db<>fiddle here

Try this using a tally table. Retrieving only the right number of records from the tally table and calculating all the dates for all the weeks in between start and end dates. Determine what week cycle it is and then join to the weekrotation table for the right week.

;with Tally (n) AS
(
    -- 100 rows
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
),
weeks as
(
    select TOP (select top 1 datepart(wk, dateend) - DATEPART(wk, datestart) from periods)
        periods.id as periodid,
        dateadd(wk, n - 1, datestart) curDate, n as wk, 
        (select max(sequencenr) from weekrotations wr2 where wr2.periodid = periods.id) as mxSequence
    from Tally
    CROSS JOIN Periods
)
select a.id, a.weekrotationid, a.dayofweek, timestart, timeend
from weeks w
inner join weekrotations wr
    on wr.periodid = w.periodid and wr.sequencenr = wk % mxSequence
inner join appointments a 
    on a.weekrotationid = wr.id

I believe this is what you are seeking. Can accomplish your desired results by using Variable Tables, Recursive CTEs, and some basic built in functions such as DATEADD, DATEPART, DATENAME, DATEDIFF and MAX. Other than that, there is some math involved for creating a 10 year calendar that can be used for to determine the appointment dates.

--DECLARATION OF VARIABLE TABLE
DECLARE @Calendar TABLE (
                            [DWDateKey] DATE
                            ,[DayDate] INT
                            ,[DayOfWeekName] VARCHAR(10)
                            ,[DayOfWeekID] INT
                            ,[WeekNumber] INT
                            ,[Year] INT
                            ,[QuarterNumber] INT
                        );

--DECLARATION OF VARIABLE TABLE
DECLARE @Reccurence TABLE (
                            [PeriodID] INT
                            ,[ReccurenceByWeek] INT
                            );

--GET MAX SEQUENCE NUMBER BY PERIOD ID
INSERT INTO @Reccurence
SELECT PeriodID
      ,MAX(SequenceNr)
FROM WeekRotations AS WR
GROUP BY PeriodID;

--RECURSIVE COMMON TABLE EXPRESION (CTE) TO POPULATE A 10 YEAR CALENDAR
WITH CTE_DatesTable
AS
(
  SELECT CAST(DATEADD(yy, DATEDIFF(yy, 0, SYSDATETIME()), 0) AS DATE) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= CAST(DATEADD(YY,10,DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy, 0, SYSDATETIME()) +1, 0))) AS DATE)
)
INSERT INTO @Calendar
SELECT [DWDateKey] = [date]
          ,[DayDate] = DATEPART(dd,[date])
          ,[DayOfWeekName] = DATENAME(dw,[date])
          ,[DayOfWeekID] = ((DATEPART(dw,[date]) + 5) % 7 + 1) - 1
          ,[WeekNumber] = DATEPART(WEEK, [date])         
          ,[Year] = DATEPART(YY,[date])      
          ,[QuarterNumber] = DATEPART(QUARTER, [date])
FROM CTE_DatesTable AS CTE
OPTION (MAXRECURSION 0);  


--RECURSIVE COMMON TABLE EXPRESION (CTE) TO POPULATE LISTING OF APPPINTMENT DATES
;WITH 
CTE_FinalResult 
AS
(
SELECT SubQry.CustomerFirstName
       ,SubQry.CustomerLastName
       ,SubQry.AppointmentDate
       ,SubQry.AppointmentStartTime
       ,SubQry.AppointmentEndTime
       ,SubQry.AppointmentID
       ,SubQry.WeekRotationID
       ,SubQry.PeriodID
       ,SubQry.DateEnd
FROM 
    (
    SELECT Customer.FirstName AS CustomerFirstName
            , Customer.LastName  AS CustomerLastName
            , Calendar.DWDateKey AS AppointmentDate
            , CONVERT(VARCHAR(10), CAST(Appointments.TimeStart AS TIME), 100) AS AppointmentStartTime
            , CONVERT(VARCHAR(10), CAST(Appointments.TimeEnd AS TIME), 100) AS AppointmentEndTime
            , Appointments.ID AS AppointmentID
            , WeekRotations.ID AS WeekRotationID
            , Period.ID AS PeriodID  
            , Period.DateEnd 
            , RANK() OVER (PARTITION BY Customer.CustomerID ORDER BY Calendar.DWDateKey) AS RNK
    FROM @Calendar AS Calendar
    JOIN Period
        ON Calendar.DWDateKey BETWEEN Period.DateStart AND Period.DateEnd
    JOIN Customer
        ON Period.CustomerID = Customer.CustomerID
    JOIN WeekRotation
        ON Period.ID = WeekRotations.PeriodID
    JOIN Appointments AS Appointments
        ON WeekRotations.ID = Appointments.WeekRotationID
        AND Appointments.[DayOfWeek] = Calendar.[DayOfWeekID]
    ) SubQry
WHERE SubQry.RNK = 1

UNION ALL 

SELECT CTE_FinalResult.CustomerFirstName
        , CTE_FinalResult.CustomerLastName
        , DATEADD(WEEK, R.ReccurenceByWeek, CTE_FinalResult.AppointmentDate) AS AppointmentDate
        , CTE_FinalResult.AppointmentStartTime
        , CTE_FinalResult.AppointmentEndTime
        , CTE_FinalResult.AppointmentID
        , CTE_FinalResult.WeekRotationID
        , CTE_FinalResult.PeriodID
        , CTE_FinalResult.DateEnd
FROM CTE_FinalResult
JOIN @Reccurence AS R
    ON CTE_FinalResult.PeriodID = R.PeriodID 
WHERE DATEADD(WEEK, R.ReccurenceByWeek, CTE_FinalResult.AppointmentDate) <= CTE_FinalResult.DateEnd
)

--FINAL SELECT STATEMENT
SELECT FinalResult.CustomerFirstName
        ,FinalResult.CustomerLastName
        ,FinalResult.AppointmentDate
        ,FinalResult.AppointmentStartTime
        ,FinalResult.AppointmentEndTime
        ,FinalResult.AppointmentID
        ,FinalResult.WeekRotationID
        ,FinalResult.PeriodID
FROM CTE_FinalResult AS FinalResult
ORDER BY PeriodID 
         ,AppointmentDate
OPTION (MAXRECURSION 0);

Here is the code with some example data that can be ran for testing by using variable tables to store test data.

--EXAMPLE DATA FOR TESTING PURPOSES

DECLARE @Period TABLE (ID INT, DateStart DATE, DateEnd DATE, CustomerID INT)
DECLARE @WeekRotation TABLE (ID INT, PeriodID INT, SequenceNr INT)
DECLARE @Appointment TABLE (ID INT, WeekRotationID INT, [DayOfWeek] INT, TimeStart SMALLDATETIME, TimeEnd SMALLDATETIME)
DECLARE @Customer TABLE (CustomerID INT, FirstName VARCHAR(100), LastName VARCHAR(100))

INSERT INTO @Period
VALUES (1,'2021-01-01','2021-03-31',1)
,(2,'2021-01-01','2021-03-31',2)
,(3,'2021-01-01','2021-03-31',3)

INSERT INTO @WeekRotation
VALUES (100,1,1)
,(101,2,1)
,(102,2,2)
,(103,3,1)
,(104,3,2)
,(105,3,3)

INSERT INTO @Appointment
VALUES (500,100,0,'1900-01-01 08:00','1900-01-01 10:00')
,(500,101,2,'1900-01-01 09:00','1900-01-01 11:00')
,(500,103,5,'1900-01-01 10:00','1900-01-01 13:00')

INSERT INTO @Customer
VALUES (1,'John','Doe')
,(2,'Jane','Smith')
,(3,'Jake','Weston')

---------------------------------------------

--DECLARATION OF VARIABLE TABLE
DECLARE @Calendar TABLE (
                            [DWDateKey] DATE
                            ,[DayDate] INT
                            ,[DayOfWeekName] VARCHAR(10)
                            ,[DayOfWeekID] INT
                            ,[WeekNumber] INT
                            ,[Year] INT
                            ,[QuarterNumber] INT
                        );

--DECLARATION OF VARIABLE TABLE
DECLARE @Reccurence TABLE (
                            [PeriodID] INT
                            ,[ReccurenceByWeek] INT
                            );

--GET MAX SEQUENCE NUMBER BY PERIOD ID
INSERT INTO @Reccurence
SELECT PeriodID
      ,MAX(SequenceNr)
FROM @WeekRotation AS WR
GROUP BY PeriodID;

--RECURSIVE COMMON TABLE EXPRESION (CTE) TO POPULATE A 10 YEAR CALENDAR
WITH CTE_DatesTable
AS
(
  SELECT CAST(DATEADD(yy, DATEDIFF(yy, 0, SYSDATETIME()), 0) AS DATE) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= CAST(DATEADD(YY,10,DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy, 0, SYSDATETIME()) +1, 0))) AS DATE)
)
INSERT INTO @Calendar
SELECT [DWDateKey] = [date]
          ,[DayDate] = DATEPART(dd,[date])
          ,[DayOfWeekName] = DATENAME(dw,[date])
          ,[DayOfWeekID] = ((DATEPART(dw,[date]) + 5) % 7 + 1) - 1
          ,[WeekNumber] = DATEPART(WEEK, [date])         
          ,[Year] = DATEPART(YY,[date])      
          ,[QuarterNumber] = DATEPART(QUARTER, [date])
FROM CTE_DatesTable AS CTE
OPTION (MAXRECURSION 0);  


--RECURSIVE COMMON TABLE EXPRESION (CTE) TO POPULATE LISTING OF APPPINTMENT DATES
;WITH 
CTE_FinalResult 
AS
(
SELECT SubQry.CustomerFirstName
       ,SubQry.CustomerLastName
       ,SubQry.AppointmentDate
       ,SubQry.AppointmentStartTime
       ,SubQry.AppointmentEndTime
       ,SubQry.AppointmentID
       ,SubQry.WeekRotationID
       ,SubQry.PeriodID
       ,SubQry.DateEnd
FROM 
    (
    SELECT Customer.FirstName AS CustomerFirstName
            , Customer.LastName  AS CustomerLastName
            , Calendar.DWDateKey AS AppointmentDate
            , CONVERT(VARCHAR(10), CAST(Appointments.TimeStart AS TIME), 100) AS AppointmentStartTime
            , CONVERT(VARCHAR(10), CAST(Appointments.TimeEnd AS TIME), 100) AS AppointmentEndTime
            , Appointments.ID AS AppointmentID
            , WeekRotations.ID AS WeekRotationID
            , Period.ID AS PeriodID  
            , Period.DateEnd 
            , RANK() OVER (PARTITION BY Customer.CustomerID ORDER BY Calendar.DWDateKey) AS RNK
    FROM @Calendar AS Calendar
    JOIN @Period AS Period
        ON Calendar.DWDateKey BETWEEN Period.DateStart AND Period.DateEnd
    JOIN @Customer AS Customer
        ON Period.CustomerID = Customer.CustomerID
    JOIN @WeekRotation AS WeekRotations
        ON Period.ID = WeekRotations.PeriodID
    JOIN @Appointment AS Appointments
        ON WeekRotations.ID = Appointments.WeekRotationID
        AND Appointments.[DayOfWeek] = Calendar.[DayOfWeekID]
    ) SubQry
WHERE SubQry.RNK = 1

UNION ALL 

SELECT CTE_FinalResult.CustomerFirstName
        , CTE_FinalResult.CustomerLastName
        , DATEADD(WEEK, R.ReccurenceByWeek, CTE_FinalResult.AppointmentDate) AS AppointmentDate
        , CTE_FinalResult.AppointmentStartTime
        , CTE_FinalResult.AppointmentEndTime
        , CTE_FinalResult.AppointmentID
        , CTE_FinalResult.WeekRotationID
        , CTE_FinalResult.PeriodID
        , CTE_FinalResult.DateEnd
FROM CTE_FinalResult
JOIN @Reccurence AS R
    ON CTE_FinalResult.PeriodID = R.PeriodID 
WHERE DATEADD(WEEK, R.ReccurenceByWeek, CTE_FinalResult.AppointmentDate) <= CTE_FinalResult.DateEnd
)

--FINAL SELECT STATEMENT
SELECT FinalResult.CustomerFirstName
        ,FinalResult.CustomerLastName
        ,FinalResult.AppointmentDate
        ,FinalResult.AppointmentStartTime
        ,FinalResult.AppointmentEndTime
        ,FinalResult.AppointmentID
        ,FinalResult.WeekRotationID
        ,FinalResult.PeriodID
FROM CTE_FinalResult AS FinalResult
ORDER BY PeriodID 
         ,AppointmentDate
OPTION (MAXRECURSION 0);

Results From Test Data and Code with Variable Tables

The basic idea is to unfold all the days in the period, then calculate the week day and the week No relative to the period start date for each date, then we can easily identify the date with scheduled appointments by checking week No and week day against week rotation and appointments settings.

Here is the query with your third example data -

with Period as (
select *
  from (VALUES (1,'2021-01-01','2021-03-31',1)
       ) p(ID, DateStart, DateEnd, CustomerID) 
)
,WeekRotation as (
select *
  from (VALUES (100,1,1)
              ,(101,1,2)
              ,(102,1,3)
       ) w(ID, PeriodID, SequenceNr)
)
,Appointment as (
select a.ID
      ,a.WeekRotationID
      ,case when DayOfWeek + 2 <= 7 then DayOfWeek + 2 else 1 end DayOfWeek -- align DateOfWeek with DatePart(weekday, date) 
      ,a.TimeStart
      ,a.TimeEnd
  from (VALUES (500,100,0,'1900-01-01 08:00','1900-01-01 10:00')
       ) a(ID, WeekRotationID, DayOfWeek, TimeStart, TimeEnd)
)
,schedule as (
   select p.ID            PeriodID
         ,w.SequenceNr    WeekNo
         ,a.DayOfWeek
         ,a.TimeStart
         ,a.TimeEnd
     from Period p 
     join WeekRotation w on p.ID = w.PeriodID
left join Appointment  a on w.ID = a.WeekRotationID
)
,UnfoldDate as (
select p.ID        PeriodID
      ,p.DateStart PeriodStartDate
      ,DateAdd(day, seq.number - 1, p.DateStart) date
  from Period p
      ,(select distinct number FROM master..[spt_values] where number BETWEEN 1 AND 1000) seq -- a quick way to get sequence numbers, replace it with your own tally table on production. Also assume the period length is less than 1000 days
 where seq.number <=  DateDiff(day, p.DateStart, p.DateEnd) + 1
)
,RotationCount as (
select PeriodID
      ,count(0) Cnt
  from WeekRotation
 group
    by PeriodID
)
,DateTable as (
select ud.PeriodID
      ,ud.date
      ,datediff(week, ud.PeriodStartDate, ud.date) % rc.cnt + 1 WeekNo
      ,datepart(weekday, date) DayOfWeek
  from UnfoldDate    ud
      ,RotationCount rc
 where ud.PeriodID = rc.PeriodID
)
select d.periodID
      ,d.date
      ,s.TimeStart
      ,s.TimeEnd
  from schedule  s
      ,DateTable d
 where s.PeriodID = d.PeriodID
   and s.WeekNo = d.WeekNo
   and s.DayOfWeek = d.DayOfWeek
 order
    by d.periodID
      ,d.date
      ,s.TimeStart

The output is -

1   2021-01-18 00:00:00.000 1900-01-01 08:00    1900-01-01 10:00
1   2021-02-08 00:00:00.000 1900-01-01 08:00    1900-01-01 10:00
1   2021-03-01 00:00:00.000 1900-01-01 08:00    1900-01-01 10:00
1   2021-03-22 00:00:00.000 1900-01-01 08:00    1900-01-01 10:00