Attendance details for employee

Question

I have two tables 1)tblEmployeeMaster EmployeeCode EmployeeName EMP001 Prakash EMP002 yuvaraj EMP003 siva EMP004 nirmal EMP005 sathya EMP006 anand 2)tblTime UserId TimeIn TimeDate EMP001 10:30AM 30-08-2016 EMP002 10:10AM 30-08-2016 OutPut::::: EmployeeCode EmployeeName Status EMP001 Prakash Present EMP002 Yuvaraj Present EMP003 siva Absent EMP004 nirmal Absent EMP005 sathya Absent EMP006 anand Absent Please help me.....

Nitin Sontakke · Answer

select EmployeeCode EmployeeName Case when tt.TimeIn is Null then 'Absent' else 'Present' end Status from tblEmployeeMaster em left outer join tblTime tt on em.EmployeeCode = tt.UserId and tt.TimeDate = '2016-08-30' May be you would like to send tt.TimeDate as a parameter.

Vinay Singh · Answer

check the query select EmployeeCode EmployeeName CASE tt.UserId WHEN null THEN 'Absent' else 'Present' END AS TargetAliasColumnName Status from tblEmployeeMaster em left outer join tblTime tt on em.EmployeeCode=tt.UserId