TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Akhter HUssain
720
1.3k
102.1k
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..
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
Reply
Answers (
4
)
case when condition check for overtime allow in employee.
need to get 15 years back date from datetime