Attendance Summary Report on LIMTON TIS

Here is quick SQL query that generates employee’s monthly attendance report on Limton TIS. Getting Department and Designation names was a bit tricky as they are not named as such. They are placed inside HRTCompanyLogicDetail table.

select datename(dw,A.RDate) Day, C.EmployeeFirstName + ' '+ C.EmployeeLastName as EmployeeName, B.*,D.Designation, E.Department,  datediff(hh,InTime,OutTime) Hours, A.Remarks from 
(
	Select * from HRTAttendance
) A
JOIN
(
	select EmployeeNo, RDate, min(PunchIn) InTime, max(PunchIn) OutTime from 
	(
	select convert(varchar(10),RDate,20) RDate,PunchIn, EmployeeNo  from TISAttendanceView
	union
	select * from openquery("x.x.x.x",'select convert(varchar(10),A.ScanDateTime,20) RDate,  A.ScanDateTime, B.EmployeeNo from PIMC.dbo.AttendanceCards A JOIN PIMC.dbo.Employee_Cards B on A.CardNo=B.CardNo')
	) T
	where EmployeeNo=2050  and month(RDate) = 4 and YEAR(RDate) = 2015
group by EmployeeNo, RDate
) B ON
convert(varchar(10),A.RDate,20)=B.RDate and A.EmployeeNo = B.EmployeeNo
JOIN HRTEmployee C
	on B.EmployeeNo = C.EmployeeNo 
JOIN
(
	select EmployeeNo, BB.Description as Designation from HRTEmployeeDetail AA JOIN 	HRTCompanyLogicDetail BB on AA.CompanyLogicID= BB.CompanyLogicID and BB.TypeCode='Des1'
) D
On C.EmployeeNo = D.EmployeeNo
JOIN
(
	select EmployeeNo, BB.Description As Department from HRTEmployeeDetail AA JOIN 	HRTCompanyLogicDetail BB on AA.CompanyLogicID= BB.CompanyLogicID and BB.TypeCode='Dep1'
) E
On C.EmployeeNo = E.EmployeeNo

This report does contain on query from my own database. Basically were are combining attendance report from TIS and our own attendance system.

This entry was posted in Tutorials/Tips and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s