SQL Server 考勤SQL语句从到

9wbgstp7 于 2022-11-28 发布在其他

关注(0)|答案(1)|浏览(140)

我们使用MS SQL 2008 r2，我们有这2个表用于考勤
第一个
我们需要从这两个表中检查日期、小鸡进出和传感器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-server

来源：https://stackoverflow.com/questions/50078523/time-attendance-sql-statement-from-to

1条答案

按热度按时间

monwx1rj1#

这可能会得到所需的结果

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'

赞(0）回复(0）举报 2022-11-28

我来回答

SQL Server 考勤SQL语句从到

1条答案

相关问题

热门标签

最新问答