How to Update Table using Left Join between two table?

Question

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.. Update EmployeesAttendance set EmployeesAttendance.INTIME=@INTime, EmployeesAttendance.OUTTIME=@OUTTIME where EmployeesAttendance.AttdID=EmployeesAttendance.AttdID AND Case When (DATEDIFF( Hour , INTIME,OUTTIME)) = Hours CASE WHEN EmployeesAttendance.Hours >= 8 THEN 1 WHEN EmployeesAttendance.Hours = 0 THEN 0 WHEN EmployeesAttendance.Hours >= 6 THEN 0.5 = Day , CASE WHEN EmployeesAttendance.Hours > EmployeeDetails.Dhour then EmployeesAttendance .Hours - EmployeesAttendance .Dhour else 0 End = OT, CASE when EmployeeDetails.OTEntitled = 'Yes' AND EmployeesAttendance .Hours >= EmployeeDetails.Dhour THEN (( EmployeesAttendance.Hours - 8) * 100) else 0 END = OTAmount, Convert ( varchar (10), EmployeesAttendance .INTIME,108) = Time , Case When Convert ( Time , EmployeesAttendance .INTIME,108) > cte.LTime Then 1 else 0 end =Late from EmployeeDetails Left Join EmployeesAttendance ON EmployeeDetails.EmpId = EmployeesAttendance.EmpID order by EmployeeDetails.EmpID asc

Suraj Kumar · Answer

What is the error you are getting? Also try the following query Update EmployeesAttendance set EmployeesAttendance.INTIME=@INTime, EmployeesAttendance.OUTTIME=@OUTTIME where EmployeesAttendance.AttdID=EmployeesAttendance.AttdID AND Case When (DATEDIFF( Hour , INTIME,OUTTIME)) = Hours CASE WHEN EmployeesAttendance.Hours >= 8 THEN 1 WHEN EmployeesAttendance.Hours = 0 THEN 0 WHEN EmployeesAttendance.Hours >= 6 THEN 0.5 = Day , CASE WHEN EmployeesAttendance.Hours > emp.Dhour then EmployeesAttendance .Hours - EmployeesAttendance .Dhour else 0 End = OT, CASE when emp.OTEntitled = 'Yes' AND EmployeesAttendance .Hours >= emp.Dhour THEN (( EmployeesAttendance.Hours - 8) * 100) else 0 END = OTAmount, Convert ( varchar (10), EmployeesAttendance .INTIME,108) = Time , Case When Convert ( Time , EmployeesAttendance .INTIME,108) > cte.LTime Then 1 else 0 end =Late from EmployeeDetails as emp Left Join EmployeesAttendance attendance ON emp.EmpId = attendance.EmpID order by emp.EmpID asc

Akhter HUssain · Answer

cteFinal AS ( select cte.EmpID,cte2.ReportingDate,cte2. Date ,cte2.INTIME,cte2.OUTTIME, CTE.Dhour ,CTE.OTRate, DATEDIFF( Hour , @INTIME, @OUTTIME) as [Hours] , CASE WHEN cte2.[Hours] >= CTE.Dhour THEN 1 WHEN cte2.[Hours] = 0 THEN 0 WHEN cte2.[Hours] >= 6 THEN 0.5 END AS [Days], CASE WHEN cte2.[Hours] > CTE.Dhour then cte2.[Hours] - CTE.Dhour else 0 End as OT, CASE when cte.OTEntitled = 'Yes' AND cte2.[Hours] >= CTE.Dhour THEN (( cte2.[Hours] - 8) * 100) else 0 END AS OTAMount, Convert ( varchar (10), cte2.INTIME,108) as [ Time ], Case When Convert ( Time , cte2.INTIME,108) > cte.LTime Then 1 else 0 end as Late from cte INNER Join cte2 ON cte2.EmpId= cte.EmpID ) UPDATE cte2 SET cte2.INTIME = @INTIME, cte2.OUTTIME=@OUTTIME, cte2.Hours=DATEDIFF( Hour , @INTIME, @OUTTIME), cte2.Days = CASE WHEN DATEDIFF( Hour , @INTIME, @OUTTIME) >= CTE.Dhour THEN 1 WHEN cte2.[Hours] = 0 THEN 0 WHEN cte2.[Hours] >= 6 THEN 0.5 END , cte2.OT= CASE WHEN DATEDIFF( Hour , @INTIME, @OUTTIME) > CTE.Dhour then DATEDIFF( Hour , @INTIME, @OUTTIME) - CTE.Dhour else 0 End , cte2.OTAmount = CASE when cte.OTEntitled = 'Yes' AND DATEDIFF( Hour , @INTIME, @OUTTIME) >= CTE.Dhour THEN (( DATEDIFF( Hour , @INTIME, @OUTTIME)- 8) * cte.OTRate) else 0 END , cte2. Time = Convert ( varchar (10), @INTIME,108), cte2.Late = Case When Convert ( varchar (10), @INTIME,108) > cte.LTime Then 1 else 0 end from cte INNER Join cte2 ON cte2.EmpId= cte.EmpID where cte2.AttdID=@AttdID

Suraj Kumar · Answer

Have you tried with my query and please put your schema as table structure.

Akhter HUssain · Answer

I have created Store Procedure but it just updating INTIME and OUTIME rest of column value are not getting calculate in table as i given condition defined in query Update EmployeesAttendance set EmployeesAttendance.INTIME=@INTime, EmployeesAttendance.OUTTIME=@OUTTIME, EmployeesAttendance.Hours= ( (DATEDIFF(Hour,INTIME,OUTTIME))), EmployeesAttendance.Day=( CASE WHEN EmployeesAttendance.Hours >= 8 THEN 1 WHEN EmployeesAttendance.Hours = 0 THEN 0 WHEN EmployeesAttendance.Hours >= 6 THEN 0.5 end), EmployeesAttendance.OT=(CASE WHEN EmployeesAttendance.Hours > EmployeeDetails.Dhour then EmployeesAttendance.Hours - EmployeeDetails.Dhour else 0 End), EmployeesAttendance.OTAmount =(CASE when EmployeeDetails.OTEntitled = 'Yes' AND EmployeesAttendance .Hours >= EmployeeDetails.Dhour THEN (( EmployeesAttendance.Hours - 8) * 100) else 0 END ), EmployeesAttendance.Time=( Convert(varchar(10),EmployeesAttendance.INTIME,108)), EmployeesAttendance.Late=(Case When Convert(Time, EmployeesAttendance .INTIME,108) > EmployeeDetails.LTime Then 1 else 0 end) From EmployeeDetails Left Join EmployeesAttendance ON EmployeeDetails.EmpId = EmployeesAttendance.EmpID -- Order by EmployeeDetails.EmpID asc where EmployeesAttendance.AttdID=@AttdID