Shift chnage - 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)

Shift chnage

Author

Topic

vipinjha123
Starting Member

45 Posts

Posted - 2012-06-12 : 03:07:36

Dear All,
please suggest
suppose employee A123 working shift is Day which start from 8am to 5pm
and if he work in another shift for 2-3 days
how will i track all those employee who work other than theri own shift

I am looking for query where i will capture all employee who work another than their own shift
my query is

select DISTINCT
TAB.RTT_EMP_STAFFID EMP_ID,
TAB.EMP_FIRSTNAME + ' '+ isnull(TAB.EMP_MIDDLENAME,'') + ' '+ isnull(TAB.EMP_LASTNAME,'') "Employee name",
TAB.RTT_ATTENDANCE_DATE "Attendence Date",
A.IN_DATE 'In Login time into HR system',
A.OUT_DATE 'out Login time into HR system',
SM.SHIFT_NAME AS "Base Shift",
SH.SHIFT_GROUP,
SH.SHIFT_START_TIME,
B.RTT_STATUS Status,
PR.OU_NAME AS "Project",
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 MP_SHIFT_MASTER SM ON SM.SHIFT_CODE=E.EMP_SHIFTCODE
INNER JOIN ERM_OU_MASTER PR ON E.OU_ID=PR.OU_ID
INNER 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
INNER join SHIFT_MASTER SH ON SH.SHIFT_CODE=E.EMP_SHIFTCODE

WHERE B.RTT_EMP_STAFFID='A0766'
AND B.RTT_ATTENDANCE_DATE>='2012-04-16'
and B.RTT_ATTENDANCE_DATE<='2012-05-15'
ORDER BY TAB.RTT_ATTENDANCE_DATE DESC

regards,
Vipin jha

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-12 : 07:36:27

I must admit that I have not been able to follow 100% of the logic you are trying to implement because there are several tables and the relationships and columns are unknown to me. However, I would think that the basic logic you want to implement is something like this:

SELECT
	a.EMP_STAFFID,
	a.ATTENDANCE_DATE,
	a.IN_DATE,
	a.OUT_DATE
FROM
	ATTENDANCE_REGISTER a
	INNER JOIN MP_SHIFT_MASTER m ON
		m.EMP_STAFFID = a.EMP_STAFFID
		AND m.ATTENDANCE_DATE = a.ATTENDANCE_DATE
WHERE
	a.IN_DATE >= b.SHIFT_END_TIME -- checked in after end of scheduled shift
	OR a.OUT_DAT <= b.SHIFT_START_TIME -- checked out before start of scheduled shift

I am assuming that there is a table with the scheduled shift start and end time for each day for each employee (MP_SHIFT_MASTER) and another table that has the actual time stamps of when the employee checked in and checked out.

You may need to modify/enhance it to take care of things such as:
a) what if the employee stayed through his/her shift and continued on to the next shift?
b) what if the employee did not even show up for work

etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources