|
vipinjha123
Starting Member
India
45 Posts |
 Posted - 06/20/2012 : 00:58:30
|
Dear All,
in my below query i am not getting data on maximum a.in_date.
i am looking to get only those employee who is swipin to office letter and login to hr system first and vice varsa who swipeout to office first and logot to hr leter.
below is my query
select
TAB.RTT_EMP_STAFFID,
TAB.EMP_FIRSTNAME + ' '+ isnull(TAB.EMP_MIDDLENAME,'') + ' '+ isnull(TAB.EMP_LASTNAME,'') "Employee name",
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.EMP_FIRSTNAME + ' '+ isnull(TAB.EMP_MIDDLENAME,'') + ' '+ isnull(TAB.EMP_LASTNAME,'') ,
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
Regards,
Vipin jha
|
|
Login hr first swipe in leter query
