出勤进出 [英] Attendance IN and OUT
本文介绍了出勤进出的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
考虑以下表结构和示例数据 -
Consider the following table structure and sample data -
EmpID InputDateTime StatusINOUT
-------------------------------------
1 2018-05-26 08:44 1
1 2018-05-26 08:44 2
2 2018-05-28 08:44 1
2 2018-05-28 12:44 2
1 2018-05-21 08:44 1
1 2018-05-21 10:44 2
2 2018-05-23 08:44 1
2 2018-05-23 08:44 2
现在我想将 InputDateTime
列分成两列,即 INTIME(1)
和 OUTTIME(2)
.这背后的逻辑是 StatusInOut
为 1 的日期将是 InTime
而对于 StatusInOut
是 2,日期值将是 OUTTIME(2)
.
Now I want to separate column InputDateTime
into two columns i.e., INTIME(1)
and OUTTIME(2)
. The logic behind this is the date for which StatusInOut
is 1 will be InTime
and for StatusInOut
is 2 that date value will be OUTTIME(2)
.
预期的输出格式如下:
Empid INTIME(1) OUTIME(2)
--------------------------------------------
1 2018-05-26 08:44 2018-05-26 08:44
2 2018-05-28 08:44 2018-05-28 12:44
1 2018-05-21 08:44 2018-05-21 10:44
2 2018-05-23 08:44 2018-05-23 08:44
推荐答案
尝试使用 join 和 update 执行以下查询.
Try the following query using join and update.
create table #tempStatus (EmpId int, intTime datetime, sStatus int)
insert into #tempStatus
values(1, '2018-05-26 08:44', 1),
(1, '2018-05-26 08:44', 2),
(2, '2018-05-28 08:44', 1),
(2, '2018-05-28 12:44', 2),
(1, '2018-05-21 08:44', 1),
(1, '2018-05-21 10:44', 2),
(2, '2018-05-23 08:44', 1),
(2, '2018-05-23 08:44', 2)
,(3, '2018-05-23 08:44', 1)
select EmpId, MIN(intTime) as intTime, MAX(intTime) as OutTime into #tempA from (
select EmpId, intTime, intTime as OutTime
from #tempStatus where sStatus = 1
)a
group by EmpId, intTime
update s
set s.OutTime = t.outTime
from #tempA s
left join
(
select EmpId, MAX(outTime) as outTime from(
select EmpId, intTime as outTime
from #tempStatus where sStatus = 2
)b
group by empId,outTime) t
on s.EmpId = t.EmpId and Convert(Varchar,s.OutTime,112) = Convert(Varchar,t.outTime,112)
select * from #tempA order by EmpId
drop table #tempA
DROP TABLE #tempStatus
或者你也可以试试下面的一个查询
OR you can also try the below one query
select empid,
max(case when sStatus = 1 then intTime end) as INTIME,
max(case when sStatus = 2 then intTime end) as OUTIME
from (select t.*,
row_number () over ( order by inttime) as seq1,
row_number () over (partition by empid order by inttime) as seq2
from #tempStatus t
) t
group by empid, (seq1-seq2);
检查类似的答案 - 此处
Check for the similar answers - here
这篇关于出勤进出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文