SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table periods (id int primary key, datestart date,dateend date ,customerid int);
create table WeekRotations (id int primary key, periodid int , sequencenr int, foreign key (periodid) references periods(id));
create table Appointments (id int primary key,WeekRotationID int ,DayOfWeek int,timestart datetime,timeend datetime,foreign key (WeekRotationID) references WeekRotations(id));


insert into periods values(1      ,'2021-01-01','2021-03-31',   99);

insert into WeekRotations values(100     ,1,             1);

insert into Appointments values(500     ,100            ,0  ,   '1900-01-01 08:00','1900-01-01 10:00');

create table customers (id int,name varchar(50));
insert into customers values(99,'Romel');
4 rows affected

;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)


Calendar_date
WeekRotationID
periodid
AppointmentsID
dayofweek
timestart
timeend
maxSequenceNR
period_startdate
period_enddate
customerid
custome_rname


2021-01-04 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
1
2021-01-01
2021-03-31
99
Romel

2021-01-11 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
1
2021-01-01
2021-03-31
99
Romel

2021-01-18 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
1
2021-01-01
2021-03-31
99
Romel

2021-01-25 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
1
2021-01-01
2021-03-31
99
Romel

2021-02-01 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
1
2021-01-01
2021-03-31
99
Romel

2021-02-08 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
1
2021-01-01
2021-03-31
99
Romel

2021-02-15 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
1
2021-01-01
2021-03-31
99
Romel

2021-02-22 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
1
2021-01-01
2021-03-31
99
Romel

2021-03-01 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
1
2021-01-01
2021-03-31
99
Romel

2021-03-08 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
1
2021-01-01
2021-03-31
99
Romel

2021-03-15 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
1
2021-01-01
2021-03-31
99
Romel

2021-03-22 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
1
2021-01-01
2021-03-31
99
Romel

2021-03-29 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
1
2021-01-01
2021-03-31
99
Romel


insert into WeekRotations values(101     ,1,             2);

;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)


Calendar_date
WeekRotationID
periodid
AppointmentsID
dayofweek
timestart
timeend
maxSequenceNR
period_startdate
period_enddate
customerid
custome_rname


2021-01-11 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
2
2021-01-01
2021-03-31
99
Romel

2021-01-25 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
2
2021-01-01
2021-03-31
99
Romel

2021-02-08 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
2
2021-01-01
2021-03-31
99
Romel

2021-02-22 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
2
2021-01-01
2021-03-31
99
Romel

2021-03-08 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
2
2021-01-01
2021-03-31
99
Romel

2021-03-22 00:00:00.000
100
1
500
0
08:00:00.0000000
10:00:00.0000000
2
2021-01-01
2021-03-31
99
Romel


insert into WeekRotations values(102     ,1,             3);

;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)

366 rows affected

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


Calendar_date
WeekRotationID
periodid
AppointmentsID
dayofweek
timestart
timeend
maxSequenceNR
period_startdate
period_enddate
customerid
custome_rname


2021-01-18
100
1
500
0
08:00
10:00
3
2021-01-01
2021-03-31
99
Romel

2021-02-08
100
1
500
0
08:00
10:00
3
2021-01-01
2021-03-31
99
Romel

2021-03-01
100
1
500
0
08:00
10:00
3
2021-01-01
2021-03-31
99
Romel

2021-03-22
100
1
500
0
08:00
10:00
3
2021-01-01
2021-03-31
99
Romel



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



Calendar_date
WeekRotationID
periodid
AppointmentsID
dayofweek
timestart
timeend
maxSequenceNR
period_startdate
period_enddate
customerid
custome_rname


2021-01-18
100
1
500
0
2021-01-18 08:00
2021-01-18 10:00
3
2021-01-01
2021-03-31
99
Romel

2021-02-08
100
1
500
0
2021-02-08 08:00
2021-02-08 10:00
3
2021-01-01
2021-03-31
99
Romel

2021-03-01
100
1
500
0
2021-03-01 08:00
2021-03-01 10:00
3
2021-01-01
2021-03-31
99
Romel

2021-03-22
100
1
500
0
2021-03-22 08:00
2021-03-22 10:00
3
2021-01-01
2021-03-31
99
Romel

Calendar_date	WeekRotationID	periodid	AppointmentsID	timestart	timeend	maxSequenceNR	period_startdate	period_enddate	customerid	custome_rname
2021-01-04 00:00:00.000	100	1	500	08:00:00.0000000	10:00:00.0000000	1	2021-01-01	2021-03-31	99	Romel
2021-01-11 00:00:00.000	100	1	500	08:00:00.0000000	10:00:00.0000000	1	2021-01-01	2021-03-31	99	Romel
2021-01-18 00:00:00.000	100	1	500	08:00:00.0000000	10:00:00.0000000	1	2021-01-01	2021-03-31	99	Romel
2021-01-25 00:00:00.000	100	1	500	08:00:00.0000000	10:00:00.0000000	1	2021-01-01	2021-03-31	99	Romel
2021-02-01 00:00:00.000	100	1	500	08:00:00.0000000	10:00:00.0000000	1	2021-01-01	2021-03-31	99	Romel
2021-02-08 00:00:00.000	100	1	500	08:00:00.0000000	10:00:00.0000000	1	2021-01-01	2021-03-31	99	Romel
2021-02-15 00:00:00.000	100	1	500	08:00:00.0000000	10:00:00.0000000	1	2021-01-01	2021-03-31	99	Romel
2021-02-22 00:00:00.000	100	1	500	08:00:00.0000000	10:00:00.0000000	1	2021-01-01	2021-03-31	99	Romel
2021-03-01 00:00:00.000	100	1	500	08:00:00.0000000	10:00:00.0000000	1	2021-01-01	2021-03-31	99	Romel
2021-03-08 00:00:00.000	100	1	500	08:00:00.0000000	10:00:00.0000000	1	2021-01-01	2021-03-31	99	Romel
2021-03-15 00:00:00.000	100	1	500	08:00:00.0000000	10:00:00.0000000	1	2021-01-01	2021-03-31	99	Romel
2021-03-22 00:00:00.000	100	1	500	08:00:00.0000000	10:00:00.0000000	1	2021-01-01	2021-03-31	99	Romel
2021-03-29 00:00:00.000	100	1	500	08:00:00.0000000	10:00:00.0000000	1	2021-01-01	2021-03-31	99	Romel

Calendar_date	WeekRotationID	periodid	AppointmentsID	timestart	timeend	maxSequenceNR	period_startdate	period_enddate	customerid	custome_rname
2021-01-18	100	1	500	08:00	10:00	3	2021-01-01	2021-03-31	99	Romel
2021-02-08	100	1	500	08:00	10:00	3	2021-01-01	2021-03-31	99	Romel
2021-03-01	100	1	500	08:00	10:00	3	2021-01-01	2021-03-31	99	Romel
2021-03-22	100	1	500	08:00	10:00	3	2021-01-01	2021-03-31	99	Romel

Calendar_date	WeekRotationID	periodid	AppointmentsID	timestart	timeend	maxSequenceNR	period_startdate	period_enddate	customerid	custome_rname
2021-01-18	100	1	500	2021-01-18 08:00	2021-01-18 10:00	3	2021-01-01	2021-03-31	99	Romel
2021-02-08	100	1	500	2021-02-08 08:00	2021-02-08 10:00	3	2021-01-01	2021-03-31	99	Romel
2021-03-01	100	1	500	2021-03-01 08:00	2021-03-01 10:00	3	2021-01-01	2021-03-31	99	Romel
2021-03-22	100	1	500	2021-03-22 08:00	2021-03-22 10:00	3	2021-01-01	2021-03-31	99	Romel