新公司第一天上班，为了赢得老板赏识，sqlserver 求组大神关于一个逻辑简单的考勤语句

[wwfgu00ing]

现有表
UserName SignData
user1 2017-03-01 08:01:22
user1 2017-03-02 08:01:22
user1 2017-03-02 17:01:22
user1 2017-03-02 17:31:22
user2 2017-03-01 07:55:22
user2 2017-03-02 08:01:22
user3 2017-03-02 17:01:22
user3 2017-03-02 17:31:22

一个表里两个字段，用户名和打卡时间
业务逻辑是这样，我们一天打两次卡，早晨八点上班，下班时间是17:30
如何统计一个月内，所有用户打卡考勤时间，列出迟到十分钟，半个小时以内、早退的次数和请假的小时数
最终输出的格式是
UserName RestNum(请假小时数) TenMinutesNum HalfHourNum ZaoTuiNum

[道素]

你给的示例数据不合理吧？用户每天的数据并不是成对的，我按照我的理解修改了数据，你看看是不是这个意思？ 请假小时我没算，没理解你的意思，怎么样算请假。 思路是第一步将用户每天的登入和登出作为一行 然后在根据出和入的时间来统计各种情况

with tb(UserName,SignData)AS(
     SELECT 'user1',CONVERT(DATETIME,'2017-03-01 08:01:22') union all
     SELECT 'user1',CONVERT(DATETIME,'2017-03-02 08:11:22') union all
     SELECT 'user1',CONVERT(DATETIME,'2017-03-01 17:01:22') union all
     SELECT 'user1',CONVERT(DATETIME,'2017-03-02 17:31:22') union all
     SELECT 'user2',CONVERT(DATETIME,'2017-03-01 07:55:22') union all
     SELECT 'user3',CONVERT(DATETIME,'2017-03-01 08:01:22') union all
     SELECT 'user2',CONVERT(DATETIME,'2017-03-01 17:01:22') union all
     SELECT 'user3',CONVERT(DATETIME,'2017-03-01 17:31:22')
   ),SortSign AS(
        select *,row_number()over(partition by UserName,convert(varchar,SignData,110) order by signdata) as rn
        from tb
        where year(signdata)=2017 and month(signdata)=3
    )
    select UserName,sum(case when DATEPART(HOUR,dateadd(minute,-10,Sign_In))=8 then 1 else 0 end ) as TenMinutesNum --More than 10 minute
          ,sum(case when DATEPART(HOUR,Sign_In)=8 and DATEPART(MINUTE,Sign_In)>0 and  DATEPART(HOUR,dateadd(minute,-30,Sign_In))=7 then 1 else 0 end ) as HalfHourNum 
          ,sum(case when DATEPART(HOUR,SignOut)>=17 and DATEPART(MINUTE,SignOut)>30 then 1 else 0 end ) as ZaoTuiNum 
    from (
         select UserName, [1] as Sign_In,[2] as SignOut from SortSign
         pivot (max(SignData) for rn in([1],[2]))p   
    ) as t
    
    group by UserName

  	UserName	TenMinutesNum	HalfHourNum	ZaoTuiNum
1	user1	1	1	1
2	user2	0	0	0
3	user3	0	1	1

[道素]

那就先取出最早和最晚的一条，再去处理

with tb(UserName,SignData)AS(
     SELECT 'user1',CONVERT(DATETIME,'2017-03-01 08:01:22') union all
     SELECT 'user1',CONVERT(DATETIME,'2017-03-01 08:20:22') union all
     SELECT 'user1',CONVERT(DATETIME,'2017-03-02 08:11:22') union all
     SELECT 'user1',CONVERT(DATETIME,'2017-03-01 17:01:22') union all
     SELECT 'user1',CONVERT(DATETIME,'2017-03-02 17:31:22') union all
     SELECT 'user2',CONVERT(DATETIME,'2017-03-01 07:55:22') union all
     SELECT 'user3',CONVERT(DATETIME,'2017-03-01 08:01:22') union all
     SELECT 'user2',CONVERT(DATETIME,'2017-03-01 17:01:22') union all
     SELECT 'user3',CONVERT(DATETIME,'2017-03-01 17:31:22')
   ),SortSign AS(
        select UserName,SignData, row_number()over(partition by UserName,convert(varchar,SignData,110) order by signdata) as rn from (
            select *,row_number()over(partition by UserName,convert(varchar,SignData,110) order by signdata) as rn
                   ,count(0)over(partition by UserName,convert(varchar,SignData,110)) as cnt
            from tb
            where year(signdata)=2017 and month(signdata)=3
        )as t where rn in (1,cnt)
    )
   select UserName,sum(case when DATEPART(HOUR,dateadd(minute,-10,Sign_In))=8 then 1 else 0 end ) as TenMinutesNum --More than 10 minute
          ,sum(case when DATEPART(HOUR,Sign_In)=8 and DATEPART(MINUTE,Sign_In)>0 and  DATEPART(HOUR,dateadd(minute,-30,Sign_In))=7 then 1 else 0 end ) as HalfHourNum 
          ,sum(case when DATEPART(HOUR,SignOut)>=17 and DATEPART(MINUTE,SignOut)>30 then 1 else 0 end ) as ZaoTuiNum 
    from (
         select UserName, [1] as Sign_In,[2] as SignOut from SortSign
         pivot (max(SignData) for rn in([1],[2]))p   
         
    ) as t
    group by UserName
       

[wwfgu00ing]

一次有可能多打几次卡的

[唐诗三百首]

就测试数据来讲,请问LZ的希望结果是什么? 并解释是怎样得到的.

[Mirror然]

标题很吸引

[wwfgu00ing]

引用 3 楼 ap0405140 的回复:

就测试数据来讲,请问LZ的希望结果是什么? 并解释是怎样得到的.

打卡有可能早晨在八点之前打过一次，八点1分有可能打过一次，下班之后有可能忘记打卡，不能仅仅根据最早和最迟打卡时间记录

[wwfgu00ing]

我们这边的打卡时间是这样的： 1 我们每天早晨上班打卡时间是8:00，下班打卡时间是17:30，中午不打卡 2 每天每人可以请假两个小时，半天或者一天 那么现在跪求一个SQL语句，能统计同时每天迟到的次数和休息的小时数 就这些规则

[wwfgu00ing]

我们这边的打卡时间是这样的： 1 我们每天早晨上班打卡时间是8:00，下班打卡时间是17:30，中午不打卡 2 每天每人可以请假两个小时，半天或者一天 那么现在跪求一个SQL语句，能统计同时每天迟到的次数和休息的小时数和漏打卡次数 就这些规则