How to Update Table using Left Join between two table?

Akhter HUssain

7y827SQL

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar7y ago
Accepted 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 HUssain7y ago
1. cteFinal AS (
2. select cte.EmpID,cte2.ReportingDate,cte2.Date,cte2.INTIME,cte2.OUTTIME, CTE.Dhour ,CTE.OTRate,
3. DATEDIFF(Hour, @INTIME, @OUTTIME) as [Hours]
4. , CASE WHEN cte2.[Hours] >= CTE.Dhour THEN 1
5. WHEN cte2.[Hours] = 0 THEN 0
6. WHEN cte2.[Hours] >= 6 THEN 0.5 END AS [Days],
7. CASE WHEN cte2.[Hours] > CTE.Dhour then cte2.[Hours] - CTE.Dhour else 0 End as OT,
8. CASE when
9. cte.OTEntitled = 'Yes' AND cte2.[Hours] >= CTE.Dhour
10. THEN (( cte2.[Hours] - 8) * 100) else 0 END AS OTAMount,
11. Convert(varchar(10), cte2.INTIME,108) as [Time],
12. Case When Convert(Time, cte2.INTIME,108) > cte.LTime Then 1 else 0 end as Late
13. from cte
14. INNER Join cte2 ON cte2.EmpId= cte.EmpID
15. )
16. UPDATE cte2
17. SET cte2.INTIME = @INTIME,
18. cte2.OUTTIME=@OUTTIME,
19. cte2.Hours=DATEDIFF(Hour, @INTIME, @OUTTIME),
20. cte2.Days = CASE WHEN DATEDIFF(Hour, @INTIME, @OUTTIME) >= CTE.Dhour THEN 1
21. WHEN cte2.[Hours] = 0 THEN 0
22. WHEN cte2.[Hours] >= 6 THEN 0.5 END,
23. cte2.OT= CASE WHEN DATEDIFF(Hour, @INTIME, @OUTTIME) > CTE.Dhour
24. then DATEDIFF(Hour, @INTIME, @OUTTIME) - CTE.Dhour else 0 End,
25. cte2.OTAmount = CASE when
26. cte.OTEntitled = 'Yes' AND DATEDIFF(Hour, @INTIME, @OUTTIME) >= CTE.Dhour
27. THEN (( DATEDIFF(Hour, @INTIME, @OUTTIME)- 8) * cte.OTRate) else 0 END ,
28. cte2.Time=Convert(varchar(10), @INTIME,108),
29. cte2.Late = Case When Convert(varchar(10), @INTIME,108) > cte.LTime Then 1 else 0 end
30. from cte
31. INNER Join cte2 ON cte2.EmpId= cte.EmpID
32. where cte2.AttdID=@AttdID

Suraj Kumar7y ago

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

Akhter HUssain7y ago

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