comapre time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

comapre time

Author

Topic

vipinjha123
Starting Member

45 Posts

Posted - 2012-06-20 : 01:26:34

suppose employe A102 swipe in time is 2012-06-19 11:10:43.000 and hr login time is 2012-06-19 11:00:43.000
taht means eamployee A102 login to hr system first
but the process is first employee need to swipe then only he can login in hr system
in that case i have to copmare the both time where login_hr < login_swipe

my query is

select
TAB.RTT_EMP_STAFFID,
TAB.RTT_ATTENDANCE_DATE "Attendence Date",
convert(varchar(10), c.intime, 108) 'First In Time By Swipe Card',
convert(varchar(10), c.outtime, 108)'Last Out Time By Swipe Card',
MAX(convert(varchar(10), A.IN_DATE, 108)) Login_HR,
MAX(convert(varchar(10), A.OUT_DATE, 108)) Logout_HR,
B.RTT_STATUS Status,
round(DATEDIFF(MINUTE,IN_DATE,OUT_DATE)/60.0,2) hr_HOURS,
G.LOCATION_NAME "Location"

from ATT_RPT_CALENDAR B
LEFT JOIN ATTENDANCE_REGISTER A ON A.EMP_STAFFID=B.RTT_EMP_STAFFID AND A.ATTENDANCE_DATE=B.RTT_ATTENDANCE_DATE
LEFT JOIN iotrans C ON C.empid=A.EMP_STAFFID AND B.RTT_ATTENDANCE_DATE=C.dt
LEFT JOIN ERM_EMPLOYEE_MASTER E ON E.EMP_STAFFID=A.EMP_STAFFID
LEFT join ERM_LOCATION_MASTER G ON G.LOCATION_ID=E.EMP_LOCATION_ID
INNER JOIN (
SELECT RTT_EMP_STAFFID,RTT_ATTENDANCE_DATE, MAX(RTT_CRT_ON) DATE1,EMP.EMP_FIRSTNAME,
EMP_MIDDLENAME,EMP_LASTNAME,EMP_DATEOFJOINING,EMP_MAILID,EMP_REPORTINGTO
FROM ATT_RPT_CALENDAR
INNER JOIN ERM_EMPLOYEE_MASTER EMP ON EMP.EMP_STAFFID= ATT_RPT_CALENDAR.RTT_EMP_STAFFID
GROUP BY
RTT_ATTENDANCE_DATE,RTT_EMP_STAFFID,
EMP.EMP_FIRSTNAME,EMP_MIDDLENAME,EMP_LASTNAME,EMP_DATEOFJOINING,EMP_MAILID,EMP_REPORTINGTO
) TAB ON TAB.RTT_ATTENDANCE_DATE=B.RTT_ATTENDANCE_DATE AND TAB.DATE1 = B.RTT_CRT_ON AND TAB.RTT_EMP_STAFFID=B.RTT_EMP_STAFFID

WHERE B.RTT_EMP_STAFFID='M1402' AND
B.RTT_ATTENDANCE_DATE>='2012-05-15' and B.RTT_ATTENDANCE_DATE<='2012-06-20'

GROUP BY
TAB.RTT_EMP_STAFFID,
TAB.RTT_ATTENDANCE_DATE,
convert(varchar(10), c.intime, 108) ,
convert(varchar(10), c.outtime, 108),
B.RTT_STATUS,
round(DATEDIFF(MINUTE,IN_DATE,OUT_DATE)/60.0,2) ,
G.LOCATION_NAME
ORDER BY TAB.RTT_ATTENDANCE_DATE DESC

plesae suggest the best method of finding the same

regards,
Vipin jha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources