Creating 1 record in 1 table from multiple records in another table

[Shimshaibiz]

I am trying to populate a table with multiple records from another table. I have a table which contains records of an employee's schedule. Each record contains a single incident of a date and time for 1 employee: Fields are:

ScheduleID = unique record identifier
ScheduleName = identifies the schedule
ScheduleLine = identifies the line on the schedule grid
Position = the schedule employee position filled (not necessarily the
same as the employees postion)
ADP_ID = employee identifier
ScheduleBeginDay = what day of the week the schedule begins (usually
Monday)
ShiftName = shift worked
ScheduleStart = date and time employee is scheduled to clock in
ScheduleEnd = date and time employee is scheduled to clock out
RecordActive = is record active or deleted

I need to convert this to look like a schedule grid in the application as well as in reports. That is 1 line in the grid = up to 7 of the previous records (Monday – Sunday)
The code below works but ONLY if there is a person in the position on day 1. This is usually but not always the case. the question is "How do i create 1 line of output for each line (position) on the schedule (as long as at least 1 person is working at least 1 day in that position at least one day during the week)?

declare @startdate as datetime
set @startdate = '4/1/19'

select 
   day1.ScheduleLine,
   day1.ScheduleName,
   day1.ScheduleBeginDay,
   day1.ShiftName,
   day1.ScheduleStart,
   day1.ScheduleEnd,
   day1.Position,
   day1.ADP_ID,
   Emp1.FirstName,
   Emp1.LastName,
   day2.ADP_ID,
   Emp2.FirstName,
   Emp2.LastName,
   day3.ADP_ID,
   Emp3.FirstName,
   Emp3.LastName,
   day4.ADP_ID,
   Emp4.FirstName,
   Emp4.LastName,
   day5.ADP_ID,
   Emp5.FirstName,
   Emp5.LastName,
   day6.ADP_ID,
   Emp6.FirstName,
   Emp6.LastName,
   day7.ADP_ID,
   Emp7.FirstName,
   Emp7.LastName
from @startDate
left outer join Schedules day1
left outer join EmployeeInformation Emp1 on Emp1.ADP_ID = day1.ADP_ID
left outer join Schedules day2 on  day2.ScheduleName = day1.ScheduleName 
and day2.ScheduleLine = day1.ScheduleLine and day2.ShiftName = 
day1.ShiftName and day2.Position = day1.Position and convert(varchar(10), 
day2.ScheduleStart, 101) = CONVERT(varchar(10), @startdate + 1,101)
left outer join EmployeeInformation Emp2 on Emp2.ADP_ID = day2.ADP_ID
left outer join Schedules day3 on  day3.ScheduleName = day1.ScheduleName 
and day3.ScheduleLine = day1.ScheduleLine and day3.ShiftName = 
day1.ShiftName and day3.Position = day1.Position and convert(varchar(10), 
day3.ScheduleStart, 101) = CONVERT(varchar(10), @startdate + 2,101)
left outer join EmployeeInformation Emp3 on Emp3.ADP_ID = day3.ADP_ID
left outer join Schedules day4 on  day4.ScheduleName = day1.ScheduleName 
and day4.ScheduleLine = day1.ScheduleLine and day4.ShiftName = 
day1.ShiftName and day4.Position = day1.Position and convert(varchar(10), 
day4.ScheduleStart, 101) = CONVERT(varchar(10), @startdate + 3,101)
left outer join EmployeeInformation Emp4 on Emp4.ADP_ID = day4.ADP_ID
left outer join Schedules day5 on  day5.ScheduleName = day1.ScheduleName 
and day5.ScheduleLine = day1.ScheduleLine and day5.ShiftName = 
day1.ShiftName and day5.Position = day1.Position and convert(varchar(10), 
day5.ScheduleStart, 101) = CONVERT(varchar(10), @startdate + 4,101)
left outer join EmployeeInformation Emp5 on Emp5.ADP_ID = day5.ADP_ID
left outer join Schedules day6 on  day6.ScheduleName = day1.ScheduleName 
and day6.ScheduleLine = day1.ScheduleLine and day6.ShiftName = 
day1.ShiftName and day6.Position = day1.Position and convert(varchar(10), 
day6.ScheduleStart, 101) = CONVERT(varchar(10), @startdate + 5,101)
left outer join EmployeeInformation Emp6 on Emp6.ADP_ID = day6.ADP_ID
left outer join Schedules day7 on  day7.ScheduleName = day1.ScheduleName 
and day7.ScheduleLine = day1.ScheduleLine and day7.ShiftName = 
day1.ShiftName and day7.Position = day1.Position and convert(varchar(10), 
day7.ScheduleStart, 101) = CONVERT(varchar(10), @startdate + 6,101)
left outer join EmployeeInformation Emp7 on Emp7.ADP_ID = day7.ADP_ID
where convert(varchar(10), day1.ScheduleStart, 101) = 
CONVERT(varchar(10), @startdate, 101)
order by day1.ScheduleLine

sql sql-server
shareeditdeleteflag

Sample input:

Sample Output: