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