SQL Server 2022

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

IF OBJECT_ID('tempdb..#ScheduleChange') Is Not Null Drop Table #ScheduleChange
Create table #ScheduleChange(
    ChangeDate Date,
    EmployeeId NVARCHAR(50),
    Monday NVARCHAR(50),
    Tuesday NVARCHAR(50),
    Wednesday NVARCHAR(50),
    Thursday NVARCHAR(50),
    Friday NVARCHAR(50),
    Saturday NVARCHAR(50),
    Sunday NVARCHAR(50)
)
INSERT INTO #ScheduleChange
VALUES
    ('2024-01-02','100', '11:00 a.m.-7:00 p.m.', '11:00 a.m.-7:00 p.m.','11:00 a.m.-7:00 p.m.','11:00 a.m.-7:00 p.m.','11:00 a.m.-7:00 p.m.',NULL,'10:00 a.m.-2:00 p.m.'),
    ('2024-01-14','100','9:00 a.m-5:00 p.m','9:00 a.m-5:00 p.m','9:00 a.m-5:00 p.m','9:00 a.m-5:00 p.m','9:00 a.m-5:00 p.m',NULL,'10:00 a.m.-2:00 p.m.'),
    ('2024-01-04', '200','9:00 a.m.-5:00 p.m.','9:00 a.m.-5:00 p.m.','9:00 a.m.-5:00 p.m.','9:00 a.m.-5:00 p.m.','9:00 a.m.-5:00 p.m.','9:00 a.m.-1:00 p.m.',NULL),
    ('2024-01-10','200','10:00 a.m.-6:00 p.m.','10:00 a.m.-6:00 p.m.','10:00 a.m.-6:00 p.m.','10:00 a.m.-6:00 p.m.','10:00 a.m.-6:00 p.m.','10:00 a.m.-2:00 p.m.',NULL)

IF OBJECT_ID('tempdb..#Calendar') Is Not Null Drop Table #Calendar
Create table #Calendar(
    Date Date,
    WeekDayName NVARCHAR(15)
)

Declare @Start Date
Set @Start = '20240101'

WHILE @Start <= Cast(Getdate()-1 As Date)
BEGIN
  INSERT #Calendar
  Select @Start,
  DATENAME(DW,@Start)

Set @Start =Dateadd(day,1,@Start)
End

;WITH schedules AS (
    SELECT ChangeDate, EmployeeId, Schedule, WeekDayName
    FROM #ScheduleChange
    unpivot
    (
      Schedule
      for WeekDayName in (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)
    ) unpiv
),
employees AS (
    SELECT DISTINCT EmployeeId
    FROM #ScheduleChange
)
SELECT E.EmployeeId, C.Date, S.WeekdayName, S.Schedule, S.ChangeDate
FROM employees E
CROSS JOIN #Calendar C
CROSS APPLY (
    SELECT TOP 1 *
    FROM schedules S
    WHERE S.EmployeeId = E.EmployeeId
    AND S.ChangeDate <= C.date
    AND S.WeekDayName = C.WeekDayName
    ORDER BY S.ChangeDate DESC
) S
ORDER BY
    S.EmployeeId, 
    C.Date,
    S.ChangeDate

EmployeeId	Date	WeekdayName	Schedule	ChangeDate
100	2024-01-02	Tuesday	11:00 a.m.-7:00 p.m.	2024-01-02
100	2024-01-03	Wednesday	11:00 a.m.-7:00 p.m.	2024-01-02
100	2024-01-04	Thursday	11:00 a.m.-7:00 p.m.	2024-01-02
100	2024-01-05	Friday	11:00 a.m.-7:00 p.m.	2024-01-02
100	2024-01-07	Sunday	10:00 a.m.-2:00 p.m.	2024-01-02
100	2024-01-08	Monday	11:00 a.m.-7:00 p.m.	2024-01-02
100	2024-01-09	Tuesday	11:00 a.m.-7:00 p.m.	2024-01-02
100	2024-01-10	Wednesday	11:00 a.m.-7:00 p.m.	2024-01-02
100	2024-01-11	Thursday	11:00 a.m.-7:00 p.m.	2024-01-02
100	2024-01-12	Friday	11:00 a.m.-7:00 p.m.	2024-01-02
100	2024-01-14	Sunday	10:00 a.m.-2:00 p.m.	2024-01-14
100	2024-01-15	Monday	9:00 a.m-5:00 p.m	2024-01-14
100	2024-01-16	Tuesday	9:00 a.m-5:00 p.m	2024-01-14
100	2024-01-17	Wednesday	9:00 a.m-5:00 p.m	2024-01-14
100	2024-01-18	Thursday	9:00 a.m-5:00 p.m	2024-01-14
100	2024-01-19	Friday	9:00 a.m-5:00 p.m	2024-01-14
100	2024-01-21	Sunday	10:00 a.m.-2:00 p.m.	2024-01-14
100	2024-01-22	Monday	9:00 a.m-5:00 p.m	2024-01-14
100	2024-01-23	Tuesday	9:00 a.m-5:00 p.m	2024-01-14
100	2024-01-24	Wednesday	9:00 a.m-5:00 p.m	2024-01-14
100	2024-01-25	Thursday	9:00 a.m-5:00 p.m	2024-01-14
100	2024-01-26	Friday	9:00 a.m-5:00 p.m	2024-01-14
100	2024-01-28	Sunday	10:00 a.m.-2:00 p.m.	2024-01-14
100	2024-01-29	Monday	9:00 a.m-5:00 p.m	2024-01-14
100	2024-01-30	Tuesday	9:00 a.m-5:00 p.m	2024-01-14
200	2024-01-04	Thursday	9:00 a.m.-5:00 p.m.	2024-01-04
200	2024-01-05	Friday	9:00 a.m.-5:00 p.m.	2024-01-04
200	2024-01-06	Saturday	9:00 a.m.-1:00 p.m.	2024-01-04
200	2024-01-08	Monday	9:00 a.m.-5:00 p.m.	2024-01-04
200	2024-01-09	Tuesday	9:00 a.m.-5:00 p.m.	2024-01-04
200	2024-01-10	Wednesday	10:00 a.m.-6:00 p.m.	2024-01-10
200	2024-01-11	Thursday	10:00 a.m.-6:00 p.m.	2024-01-10
200	2024-01-12	Friday	10:00 a.m.-6:00 p.m.	2024-01-10
200	2024-01-13	Saturday	10:00 a.m.-2:00 p.m.	2024-01-10
200	2024-01-15	Monday	10:00 a.m.-6:00 p.m.	2024-01-10
200	2024-01-16	Tuesday	10:00 a.m.-6:00 p.m.	2024-01-10
200	2024-01-17	Wednesday	10:00 a.m.-6:00 p.m.	2024-01-10
200	2024-01-18	Thursday	10:00 a.m.-6:00 p.m.	2024-01-10
200	2024-01-19	Friday	10:00 a.m.-6:00 p.m.	2024-01-10
200	2024-01-20	Saturday	10:00 a.m.-2:00 p.m.	2024-01-10
200	2024-01-22	Monday	10:00 a.m.-6:00 p.m.	2024-01-10
200	2024-01-23	Tuesday	10:00 a.m.-6:00 p.m.	2024-01-10
200	2024-01-24	Wednesday	10:00 a.m.-6:00 p.m.	2024-01-10
200	2024-01-25	Thursday	10:00 a.m.-6:00 p.m.	2024-01-10
200	2024-01-26	Friday	10:00 a.m.-6:00 p.m.	2024-01-10
200	2024-01-27	Saturday	10:00 a.m.-2:00 p.m.	2024-01-10
200	2024-01-29	Monday	10:00 a.m.-6:00 p.m.	2024-01-10
200	2024-01-30	Tuesday	10:00 a.m.-6:00 p.m.	2024-01-10