需要基于另一张表的所有进出时间吗

Question

我有两个表，根据表1的输入时间和输出时间，我试图在表2中获得所有输入时间和出时间。下面的查询适用于白天的工作，但夜间到白天的输出各不相同。

表1:Daily_attendace_data

att_date    emp_code      in_time                   out_time
2018-10-21  9999    2018-10-21 08:00:00.000 2018-10-22 06:00:00.000

表2:Trnevents

emp_reader_id          DT
9999                2018-10-21 08:00:00.000
9999                2018-10-22 06:00:00.000

我的查询：

我正在使用pivot来获得所有拳击

declare @tempProcesstable as table(
[id] [nvarchar](200) NULL,
[time_stamp] datetime NULL,
[AccessType] varchar(3)  NULL) 
insert into @tempProcesstable 
select distinct t1.emp_Reader_id, t1.DT,t1.eventid from   daily_attendance_data t2 join trnevents t1
on t1.emp_reader_id=t2.emp_reader_id where   (CONVERT(VARCHAR(26), t2.att_Date, 23) >=CONVERT(VARCHAR(26), '2018-10-20', 23) 
and CONVERT(VARCHAR(26), t2.att_date, 23) <=CONVERT(VARCHAR(26), '2018-10-21', 23))
and
(t1.DT >=t2.in_time 
and  t1.DT <=t2.out_time)
--   and t1.emp_reader_id=1000
group by t1.emp_Reader_id,t1.dt,t1.eventid order by t1.emp_reader_id,DT asc
; With CheckIns
As (Select Rowemp_reader_id = Row_Number() Over (Partition by id, Cast(time_stamp As Date) Order By time_stamp),
id, time_stamp,
[Date] = Cast(time_stamp As Date),
[Time] = Cast(time_stamp As Time(0))
From @tempProcesstable)
Select Pvt.id,B.emp_name ,  [Date], CHECK1, CHECK2,Cast(dateadd(ss,datediff(ss,CHECK1,CHECK2),0) As Time(0)) Total1,
CHECK3, CHECK4,Cast(dateadd(ss,datediff(ss,CHECK3,CHECK4),0) As Time(0)) Total2

From (Select id, [Date], [Time],
CHECKNum = 'CHECK' + Cast(Rowemp_reader_id As varchar(11))
From CheckIns) As P
Pivot (Min([Time])
For CheckNum In (Check1, [Check2], Check3, Check4)
) As Pvt    
LEFT OUTER JOIN
dbo.employee AS B ON Pvt.id= B.emp_reader_id LEFT OUTER JOIN
dbo.departments AS C ON B.dept_id = C.dept_id 

我的输出：

id  emp_name    Date         CHECK1   CHECK2    Total1  CHECK3  CHECK4  Total2
9999    Test    2018-10-21  08:00:00    NULL    NULL    NULL    NULL    NULL
9999    Test    2018-10-22  06:00:00    NULL    NULL    NULL    NULL    NULL

我的预期输出：

id       att_date     Check1    Check2    Check3    Check4
9999    2018-10-21  08:00:00    06:00:00    NULL    NULL

如果我只按id分区，它会得到我预期的输出，但不是所有天都合适。我一直呆在这里，有人能帮我解决这个问题吗。

Answer 1

您可以尝试按att_date日期制作子查询中的行号，然后使用条件聚合函数制作。

SELECT emp_readr_id,
emp_name,
att_date,
MAX(CASE WHEN RN = 1 THEN time END) 'CHECK1',
MAX(CASE WHEN RN = 2 THEN time END) 'CHECK2',
MAX(CASE WHEN RN = 3 THEN time END) 'CHECK3',
MAX(CASE WHEN RN = 4 THEN time END) 'CHECK4'
FROM (
SELECT emp_readr_id,
emp_name,
att_date ,
ROW_NUMBER() OVER(PARTITION BY att_date ORDER BY DT) rn,
CONVERT(VARCHAR(10),DT,108) time
FROM Daily d 
JOIN Trnevents t on t.DT between d.in_time and d.out_time
) t1
group by emp_readr_id,
emp_name,
att_date

sqlfiddle

结果

id       att_date     Check1    Check2    Check3    Check4
9999    2018-10-21  08:00:00    06:00:00    NULL    NULL

Answer 2

尝试此查询。请注意，在这里我创建了一个临时表作为#Base。

IF OBJECT_ID('tempdb.dbo.#Base', 'U') IS NOT NULL
DROP TABLE #Base; 
CREATE table #Base
(
emp_code INT, att_date DATE, DT DATETIME, ColName VARCHAR(MAX)
)
INSERT INTO #Base
SELECT X.emp_code, X.att_date, X.DT, 'Check'+CAST(X.Num AS VARCHAR(MAX))
FROM
(
SELECT Row_Number() Over (Partition By D.emp_code, D.att_date  Order By D.emp_code, D.att_date ) As Num, *
FROM Daily_attendace_data D
INNER JOIN Trnevents T ON T.emp_reader_id = D.emp_code AND T.DT BETWEEN D.in_time 
AND D.out_time
) X
ORDER BY X.DT

DECLARE @cols AS NVARCHAR(MAX),    @query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(ColName) 
from #Base
group by ColName, DT
order by ColName
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')

set @query = N'SELECT emp_code, att_date, ' + @cols + N' from 
(
select DT, ColName, emp_code, att_date
from #Base
) x
pivot 
(
max(DT)
for ColName in (' + @cols + N')
) p '

exec sp_executesql @query;