考勤机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