mssql sqlserver 面试题-考勤信息统计脚本编写(sql脚本编写)-2


摘要:
下文讲述面试中,遇见的考勤相关数据统计的脚本编写方法
面试主要涉及知识点: master..spt_values 生成月度流水天数自动生成
datepart获取时间部分的小时数
datediff两个时间值相减获取分钟数
day获取时间日期数
左连接 子查询 等相关知识
实验环境: sqlserver 2008 R2


 ---创建门禁数据表,初始化考勤相关数据
  create table test(userId int,userName varchar(30),writeDate datetime)
  go
  insert into test (userId,userName,writeDate)values('00101','猫猫','2018-5-17 8:30:01')
  insert into test (userId,userName,writeDate)values('00101','猫猫','2018-5-17 8:30:05')
  insert into test (userId,userName,writeDate)values('00103','狗蛋','2018-5-17 8:40:01')
  insert into test (userId,userName,writeDate)values('00108','张丹','2018-5-17 8:52:01')
  insert into test (userId,userName,writeDate)values('00109','刘狗','2018-5-17 9:30:01')
  insert into test (userId,userName,writeDate)values('00110','东哥','2018-5-17 9:10:01')
  insert into test (userId,userName,writeDate)values('00112','麻花藤','2018-5-17 9:38:01')
  insert into test (userId,userName,writeDate)values('00115','雷君','2018-5-17 9:10:01')

   insert into test (userId,userName,writeDate)values('00115','雷君','2018-5-16 9:10:01')

  go

   ---面试1:统计今天迟到的员工明细 ,在考勤信息统计时,需注意,一个员工上班时间取当天打卡的最早时间 
   select * from 
     (select userId,userName,min(writeDate) as newTime from test group by userId,userName ) 
	 as testExtend
     where  datepart(hour,newTime) >8   --当天最早打卡时间,小时大于8的全部算迟到,包括9:00:00打卡的员工
     and    day(newTime) =17  
  ---面试2:统计17号迟到30分钟的员工
  select * from 
     (select userId,userName,min(writeDate) as newTime from test group by userId,userName ) 
	 as testExtend
     where   datediff(minute,'2018-5-17 9:00:00',newTime)  >30
	  and    day(newTime) =17 
	   --当天最早打卡时间,同9点相减大于30的全部为迟到三十分钟以上的员工

  ---面试3,统计本月迟到的所有员工,此时我们需要借助系统表生成一个本月的天数临时表
     select * from 
	 (
	  select dateadd(day,number,'2018-5-1') as d from master..spt_values 
       where type='p' 
        and number >=0 
        and dateadd(day,number,'2018-5-1') between '2018-4-1' and dateadd(day,-1,'2018-6-1')
     ) as a ---生成当月天流水
	 left join 
	  (select userId,userName,min(writeDate) as newTime from test
	     group by userId,userName )  b 
	  on  datediff(day,b.newTime,a.d) =0 
	  where  datepart(hour,b.newTime) >8

  go 
  truncate table test 
  drop table test 
 
mssql_sqlserver_面试考勤sql脚本相关知识编写

mssql_sqlserver_面试考勤sql脚本相关知识编写