时间戳

首页 > 解决方案 > 考勤SQL语句from to

sql-server - 考勤SQL语句from to

问题描述

我们使用 MS SQL 2008 r2，我们有这张 2 表用于考勤 

 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

我们需要从这两张表中检查日期和入住和退房以及传感器 ID

(Main(CHECKINOUT.CHECKTIME) as Checkin , Max(CHECKINOUT.CHECKTIME)as  Check out )

第一次入住和最后一次退房以及从早上 6:00 到次日凌晨 5:00 之间的最小和最大时间为 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

标签： sql-serversql-server-2008-r2

解决方案

这可能会为您提供所需的结果

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'

推荐阅读