从另一个表中的多个记录创建1个表中的1条记录

Question

我正在尝试用另一个表中的多条记录填充另一个表。我有一个表，其中包含一名员工的日程安排记录。每条记录包含1个employee的日期和时间的单个事件:字段为：

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

我需要将其转换为在应用程序和报告中看起来像一个时间表网格。这是网格中的1行=最多7条以前的记录(周一至周日)

样本输入数据: ScheduleName ScheduleBeginDay ShiftName ScheduleStart ScheduleEnd Position ADP_ID ScheduleLine Wk 2019 2019 4月1日周一第一班2019-04-01 07:00:00:00.000 2019-04-01 15:30:00.000心理健康技术中心2019年4月1周1周一第一班2019-04-02 07:00:00.000 2019-04-02 15:30:00.000心理健康技术中心2019-04-01 17:30:00.00007:00:00.000 2019-04-03 15:30:00.000心理健康科技10391 1 1自助中心2019-04-04 07:00:00:00.000 2019-04-04 15:30:00:00.000 2019 -04-04 15:30:00.000心理健康科技10391 1自助中心2019-04-05 07:00:00:00.000 2019-04-05 15:30:00.000心理健康科技10391 1自助中心2019年4月1-04-05 07:00:00:00.0002019-04-06 07:00:00.000 2019-04-06 15:30:00.000心理健康科技10244 1

输出数据示例: ScheduleLine ScheduleName ScheduleBeginDay ShiftName ScheduleStart ScheduleEnd Position ADP_ID FirstName LastName ADP_ID FirstName LastName 1 2019年4月周1星期一第一班2019-04-01 07:00:00.000 2019-04-01 15:30:00.000心理健康技术10391亚瑟·戴维斯10391亚瑟·戴维斯10391亚瑟·戴维斯10391亚瑟·戴维斯10391亚瑟·戴维斯10244埃里克·哈林10244埃里克·哈林

下面的代码是有效的，但只有在第一天有一个人在这个位置时才有效。通常情况下，但并不总是如此。问题是“我如何为时间表上的每一行(职位)创建一行输出(只要至少有一个人在该职位上工作至少一天，一周中至少有一天)？”

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

Answer 1

此代码用于从单个记录创建每周计划。在实际的实时代码中，硬编码字符串(计划名称、开始日期)实际上是由屏幕上的字段或计算字段填充的

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