Login hr first swipe in leter query

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)

Author

Topic

vipinjha123
Starting Member

45 Posts

Posted - 2012-06-20 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources