时间考勤SQL语句从
时间考勤SQL语句从
提问于 2018-04-28 16:11:09
我们使用MS 2008 r2，我们有以下两个表its用于考勤。
select CHECKINOUT.USERID ,USERINFO.USERID,CHECKINOUT.SENSORID,
userinfo.BADGENUMBER,CHECKINOUT.CHECKTIME
from CHECKINOUT , userinfo
where CHECKINOUT.USERID=userinfo.userid
and userinfo.BADGENUMBER=63 and CHECKTIME
between '2017-03-26' and '2017-04-26' USERID USERID SENSORID BADGENUMBER CHECKTIME
181 181 100 63 2017-04-01 11:02:19.000
181 181 100 63 2017-04-02 10:12:52.000
181 181 100 63 2017-04-03 10:32:43.000
181 181 1 63 2017-04-04 19:21:26.000
181 181 100 63 2017-04-05 19:54:30.000
181 181 100 63 2017-04-06 10:00:52.000
181 181 100 63 2017-04-07 09:54:49.000
181 181 100 63 2017-04-08 10:40:20.000
181 181 100 63 2017-04-11 10:21:47.000
181 181 100 63 2017-04-12 13:32:00.000
181 181 100 63 2017-04-12 19:17:13.000
181 181 100 63 2017-04-13 11:07:02.000
181 181 100 63 2017-04-13 18:34:37.000
181 181 100 63 2017-04-15 18:49:02.000
181 181 100 63 2017-04-15 18:49:10.000
181 181 1 63 2017-04-16 10:17:58.000
181 181 100 63 2017-04-18 10:31:47.000
181 181 100 63 2017-04-18 18:39:54.000
181 181 100 63 2017-04-19 10:54:00.000
181 181 100 63 2017-04-20 10:45:03.000
181 181 100 63 2017-04-22 10:25:09.000
181 181 100 63 2017-04-22 18:45:21.000
181 181 100 63 2017-04-22 18:45:26.000
181 181 100 63 2017-04-23 10:18:01.000
181 181 100 63 2017-04-24 10:20:59.000
181 181 100 63 2017-04-24 18:41:07.000
181 181 100 63 2017-04-25 11:13:48.000我们需要从这两个表中检查日期和小鸡进入、退房和传感器标识。
(Main(CHECKINOUT.CHECKTIME) as Checkin , Max(CHECKINOUT.CHECKTIME)as Check out )第一次入住和最后一次退房，从早上6:00到第二天早上5:00的Min和Max之间的时间，它的23小时，从签出到结帐的时间减为小时。
例如
USERID USERID SENSORID BADGENUMBER CHECKTIME
181 181 100 63 2017-04-01 10:02:19.000
181 181 100 63 2017-04-01 18:12:52.000
181 181 100 63 2017-04-02 10:32:43.000
181 181 100 63 2017-04-03 01:21:26.000
181 181 100 63 2017-04-04 10:54:30.000
181 181 100 63 2017-04-04 18:00:52.000
181 181 100 63 2017-04-05 09:54:49.000
181 181 100 63 2017-04-05 23:40:20.000
181 181 100 63 2017-04-06 10:21:47.000
181 181 100 63 2017-04-07 03:32:00.000像这样
BADGENUMBER SENSORID CHECKDate Checkin Checkout Hours
63 100 2017-04-01 10:02:19 18:12:52 8:10
63 100 2017-04-02 10:32:43 01:21:26 14:49
63 100 2017-04-04 10:54:30 18:00:52 07:06
63 100 2017-04-05 09:54:49 23:40:20 13:46
63 100 2017-04-06 10:21:47 03:32:00 17:11回答 1
Stack Overflow用户
发布于 2018-04-28 17:45:59
这可能会得到所需的结果。
select u.BADGENUMBER, attendance.SENSORID, attendance.CHECKDate, attendance.CheckIn ,attendance.CheckOut
,cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) / 60 as varchar) + ':' + cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) % 60 as varchar) as Minutes
from (
select temp.USERID, temp.SENSORID, temp.CHECKDate ,temp.CheckIn
,case when temp.COut = temp.CheckIn then null when temp.CheckIn is null then null else temp.COut end as CheckOut
from(
select c.USERID, c.SENSORID, convert(date, CHECKTIME) CHECKDate
,(select min(CHECKTIME) from CHECKINOUT cinout where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID and cinout.CheckTime >= dateadd(hour, 6, convert(datetime, convert(date, c.CheckTime)))) CheckIn
,(select max(CHECKTIME) from CHECKINOUT cinout where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID and cinout.CheckTime <= dateadd(hour, 29, convert(datetime, convert(date, c.CheckTime)))) COut
from CHECKINOUT c
group by c.USERID, c.SENSORID, convert(date, CHECKTIME)
)temp
) attendance
inner join userinfo u on u.USERID = attendance.USERID
where u.BADGENUMBER = 63 and CHECKDate
between '2017-03-26' and '2017-04-26'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50078523
复制相关文章
相似问题
腾讯云开发者
