|
vipinjha123
Starting Member
India
45 Posts |
 Posted - 06/20/2012 : 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 |
|
comapre time
