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.