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

shimshaibiz · May 6, 2019, 3:15pm

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

Sample Input-output:

ScottPletcher · May 6, 2019, 3:20pm

Instead of a single date, @startDate, load all dates into a dates table, then left join from those dates to the other tables.

shimshaibiz · May 7, 2019, 2:35pm

Solved: based on your suggestion: Instead of a single date, @startDate, load all dates into a dates table, then left join from those dates to the other tables. I had to take it further and create a table for each line on the schedule (not just each date).
I will post the full code shortly

shimshaibiz · May 7, 2019, 2:37pm

--this code works to create a weekly schedule from individual records.
--In the actual live code the hard-coded strings (schedule Name, start date)
--will actually be populated from fields on the screen or calculated fields

--create a temp table to hold basic data for each line on the schedule
--this is referred to as day0 and will later be joined with data from days 1 - 7
CREATE TABLE #temp_dates (
ScheduleName VARCHAR(MAX),
ScheduleLine int,
ScheduleBeginDay VARCHAR(MAX),
ShiftName VARCHAR(MAX),
Position VARCHAR(MAX),
ScheduleStart datetime,
ScheduleEnd datetime
);

--create 1 temp record for each line on the schedule and populate the basic data
insert into #temp_dates (ScheduleName,ScheduleLine,ShiftName,Position,ScheduleBeginDay)
select distinct ScheduleName,ScheduleLine,ShiftName,Position,ScheduleBeginDay
from Schedules
where ScheduleName = 'Walk-In Center April Wk 1 2019'
group by ScheduleName,ScheduleLine,ShiftName,Position,ScheduleBeginDay
order by ScheduleName,ScheduleLine,ShiftName,Position,ScheduleBeginDay

-- set date for start of weekly schedule
declare @startdate as datetime
set @startdate = '04/01/2019'

--populate the schedule start and end times for each line
update #temp_dates
set ScheduleStart = @startdate + cast(Shifts.WorkTimeStart as datetime) , ScheduleEnd = @startdate + cast(Shifts.WorkTimeEnd as datetime)
from #temp_dates
inner join Shifts
on shifts.ShiftName = #temp_dates.shiftname

--merge each temp table line with each days data for the week
USE CCAP
select
day0.ScheduleName,
day0.ScheduleLine,
day0.ShiftName,
day0.Position,
day0.ScheduleBeginDay,
format(day0.ScheduleStart,'hh:mm') as StartTime,
format(day0.ScheduleEnd,'hh:mm') as EndTime,
day0.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 #temp_dates day0
left outer join Schedules day1 on day1.ScheduleName = day0.ScheduleName and day1.ScheduleLine = day0.ScheduleLine and day1.ShiftName = day0.ShiftName and day1.Position = day0.Position and convert(varchar(10), day1.ScheduleStart, 101) = CONVERT(varchar(10), day0.ScheduleStart,101)
left outer join EmployeeInformation Emp1 on Emp1.ADP_ID = day1.ADP_ID
left outer join Schedules day2 on day2.ScheduleName = day0.ScheduleName and day2.ScheduleLine = day0.ScheduleLine and day2.ShiftName = day0.ShiftName and day2.Position = day0.Position and convert(varchar(10), day2.ScheduleStart, 101) = CONVERT(varchar(10), day0.ScheduleStart + 1,101)
left outer join EmployeeInformation Emp2 on Emp2.ADP_ID = day2.ADP_ID
left outer join Schedules day3 on day3.ScheduleName = day0.ScheduleName and day3.ScheduleLine = day0.ScheduleLine and day3.ShiftName = day0.ShiftName and day3.Position = day0.Position and convert(varchar(10), day3.ScheduleStart, 101) = CONVERT(varchar(10), day0.ScheduleStart + 2,101)
left outer join EmployeeInformation Emp3 on Emp3.ADP_ID = day3.ADP_ID
left outer join Schedules day4 on day4.ScheduleName = day0.ScheduleName and day4.ScheduleLine = day0.ScheduleLine and day4.ShiftName = day0.ShiftName and day4.Position = day0.Position and convert(varchar(10), day4.ScheduleStart, 101) = CONVERT(varchar(10), day0.ScheduleStart + 3,101)
left outer join EmployeeInformation Emp4 on Emp4.ADP_ID = day4.ADP_ID
left outer join Schedules day5 on day5.ScheduleName = day0.ScheduleName and day5.ScheduleLine = day0.ScheduleLine and day5.ShiftName = day0.ShiftName and day5.Position = day0.Position and convert(varchar(10), day5.ScheduleStart, 101) = CONVERT(varchar(10), day0.ScheduleStart + 4,101)
left outer join EmployeeInformation Emp5 on Emp5.ADP_ID = day5.ADP_ID
left outer join Schedules day6 on day6.ScheduleName = day0.ScheduleName and day6.ScheduleLine = day0.ScheduleLine and day6.ShiftName = day0.ShiftName and day6.Position = day0.Position and convert(varchar(10), day6.ScheduleStart, 101) = CONVERT(varchar(10), day0.ScheduleStart + 5,101)
left outer join EmployeeInformation Emp6 on Emp6.ADP_ID = day6.ADP_ID
left outer join Schedules day7 on day7.ScheduleName = day0.ScheduleName and day7.ScheduleLine = day0.ScheduleLine and day7.ShiftName = day0.ShiftName and day7.Position = day0.Position and convert(varchar(10), day7.ScheduleStart, 101) = CONVERT(varchar(10), day0.ScheduleStart + 6,101)
left outer join EmployeeInformation Emp7 on Emp7.ADP_ID = day7.ADP_ID
order by day0.ScheduleLine,day0.ScheduleStart