• 1.2k
• 702
• 13.2k

How to Update Table using Left Join between two table?

Jan 10 2019 1:44 AM
I have two table one is Employee Table and another one is EmployeeAttendance ,Nowi am trying to update INTIME and OUTTIME in EmployeeAttendance then result of values calculate as per query and condition ..i tried but not getting result..
1. Update EmployeesAttendance
2. set EmployeesAttendance.INTIME=@INTime,
3. EmployeesAttendance.OUTTIME=@OUTTIME
4. where EmployeesAttendance.AttdID=EmployeesAttendance.AttdID AND
5. Case When (DATEDIFF(Hour, INTIME,OUTTIME)) = Hours
6. CASE WHEN EmployeesAttendance.Hours >= 8 THEN 1
7. WHEN EmployeesAttendance.Hours = 0 THEN 0
8. WHEN EmployeesAttendance.Hours >= 6 THEN 0.5 =   Day,
9. CASE WHEN EmployeesAttendance.Hours > EmployeeDetails.Dhour then
10. EmployeesAttendance .Hours - EmployeesAttendance .Dhour else 0 End = OT,
11. CASE when EmployeeDetails.OTEntitled = 'Yes'
12. AND EmployeesAttendance .Hours >= EmployeeDetails.Dhour
13. THEN (( EmployeesAttendance.Hours - 8) * 100) else 0 END = OTAmount,
14. Convert(varchar(10), EmployeesAttendance .INTIME,108) = Time,
15. Case When Convert(Time, EmployeesAttendance .INTIME,108) > cte.LTime Then 1 else 0 end =Late
16. from EmployeeDetails
17. Left Join EmployeesAttendance ON EmployeeDetails.EmpId = EmployeesAttendance.EmpID
18. order by EmployeeDetails.EmpID asc