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.

Advertisements
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