SQL正在合并数据以获取时间线

Question

我需要组合来自不同系统的两组数据。一个来自遥控门锁系统，一个来自人力资源假日系统。

最终目标是为人们在大楼里的时间制定一个准确的时间表——我面临的困难是半天。

因此，一名员工在08:25进入大楼，在12:45离开。人力资源系统显示半天12:00至16:00(由于人力资源系统的时间间隔较大，因此时间不会一直累积(。

第一个结果将是08:25开始和16:00结束(10月1日(

第二个结果是08:00开始，16:12结束(10月2日(

。。但是假期可以是上午和下午。所以可能是相反的。我一直在与不同的MAX和MINS作斗争，并尝试使用RANK或CASE WHEN，但无法始终如一地获得正确的数字。

以下是数据的一个粗略示例

SELECT 123456 AS EmployeeID
, 'John Jones' AS EmployeeName
, '01-Oct-2018' AS DateEvent
, '08:25:00' AS EnterTime
, '12:45:00' AS LeaveTime
, 'DOOR' AS EventType
UNION ALL
SELECT 123456 AS EmployeeID
, 'John Jones' AS EmployeeName
, '01-Oct-2018' AS DateEvent
, '12:00:00' AS StartTime
, '16:00:00' AS EndTime
, 'HOL' AS EventType
UNION ALL
SELECT 123456 AS EmployeeID
, 'John Jones' AS EmployeeName
, '02-Oct-2018' AS DateEvent
, '12:30:00' AS EnterTime
, '16:12:00' AS LeaveTime
, 'DOOR' AS EventType
UNION ALL
SELECT 123456 AS EmployeeID
, 'John Jones' AS EmployeeName
, '02-Oct-2018' AS DateEvent
, '08:00:00' AS StartTime
, '12:00:00' AS EndTime
, 'HLL' AS EventType

Answer 1

查看下面的查询

select * into #tempEvent from(
SELECT 123456 AS EmployeeID
, 'John Jones' AS EmployeeName
, '01-Oct-2018' AS DateEvent
, '08:25:00' AS EnterTime
, '12:45:00' AS LeaveTime
, 'DOOR' AS EventType
UNION ALL
SELECT 123456 AS EmployeeID
, 'John Jones' AS EmployeeName
, '01-Oct-2018' AS DateEvent
, '12:00:00' AS StartTime
, '16:00:00' AS EndTime
, 'HOL' AS EventType
UNION ALL
SELECT 123456 AS EmployeeID
, 'John Jones' AS EmployeeName
, '02-Oct-2018' AS DateEvent
, '12:30:00' AS EnterTime
, '16:12:00' AS LeaveTime
, 'DOOR' AS EventType
UNION ALL
SELECT 123456 AS EmployeeID
, 'John Jones' AS EmployeeName
, '02-Oct-2018' AS DateEvent
, '08:00:00' AS StartTime
, '12:00:00' AS EndTime
, 'HLL' AS EventType
)event
select * from #tempEvent
select EmployeeID,  EmployeeName, dateevent, min(entertime) as start, max(leavetime) as finish 
from #tempEvent
group by EmployeeID,    EmployeeName, dateevent

输出如所示

EmployeeID  EmployeeName    dateevent   start       finish
123456      John Jones      01-Oct-2018 08:25:00    16:00:00
123456      John Jones      02-Oct-2018 08:00:00    16:12:00