sql查询日期考勤之间的一个月报告 [英] sql query one month report between date attendance

问题描述

select USER_ID, User_FirstName+ ' ' + User_LastName as [Last Name] , NULL as InTime, NULL as OutTime
FROM access_user
WHERE user_automatch='1' and user_id not in  --not exists "
(select  distinct e.userid as [user_id]
from access_event_logs As E
where  Convert(date,LOCALTIMESTAMP) between '03-24-2014' and '03-24-2014'
and (EVENTID ='In' or EVENTID='Out'))

Union

select distinct
E.USERID, U.User_FirstName+ ' ' + U.User_LastName as [Last name],
(select min(Convert(TIME,LOCALTIMESTAMP)) from access_event_logs As MINCE where CAST(MINCE.LOCALTIMESTAMP as DATE)=CAST(E.LOCALTIMESTAMP As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=E.USERID) as InTime,
(select max(Convert(TIME,LOCALTIMESTAMP)) from access_event_logs As MAXCE where CAST(MAXCE.LOCALTIMESTAMP as DATE)=CAST(E.LOCALTIMESTAMP As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=E.USERID) as OutTime
from access_event_logs As E
Inner join access_user as U on E.USERID = U.User_ID
WHERE   Convert(date,LOCALTIMESTAMP) between '03-24-2014' and '03-24-2014'
Order by User_ID

此查询工作正常，但选择2天以上显示报告用户ID只有一天abesent显示空值....



1月缺席显示用户ID只有一行....



如何设置这个问题.....

this query working fine, but select above 2days show report userid id only one day abesent showing null value....

1month absent showing that userid only one row....

how to set this problem.....

推荐答案

这篇关于sql查询日期考勤之间的一个月报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！