那就先取出最早和最晚的一条，再去处理
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
