Akhter HUssain

Akhter HUssain

  • 648
  • 1.3k
  • 95.4k

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 

Answers (4)