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