考勤机sql语句

 

考勤机sql语句

    SELECT   checkinout.id as 序号 ,checkinout.pin as 打卡编号,userinfo.name  姓名, checkinout.checktime 签到时间, 
    (case datepart(weekday, checktime) 
when 1  then '星期一'  
when 2  then '星期二'   
when 3  then '星期三'  
when 4  then '星期四'   
when 5  then '星期五' 
when 6  then '星期六' 
when 7 then '星期日'
end) as 星期,
    (
case checkinout.verifycode  
when '1' then '指纹' 
when '4' then '刷卡'  
else '其他'  
end 
    )   as  打卡方式,    
    (
case sn_name   
when '3288154100003' then '7号楼'  
when '3288154100001' then '8号楼' 
when '3288154100004' then '1号楼' 
when '3288154100002' then '5号楼' 
end)   as '打卡地点'  
FROM [zkeco_db].[dbo].[checkinout],[zkeco_db].[dbo].[userinfo]  
where 
    (
[zkeco_db].[dbo].[userinfo].badgenumber=checkinout.pin)   
and   
[zkeco_db].[dbo].[checkinout].pin='014120988'
and 
    checktime between  '2016/5/5 9:27:27' and '2016/5/5 9:27:27'   
order by id desc