How to update table using left join between two table?

Question

0.00/5 (No votes)

See more:

I have two table one is Employee Table and another one is EmployeeAttendance. Now ,i am trying to update INTIME and OUTTIME in EmployeeAttendance then rest of column values calculate as per query and condition ..i tried but not getting result..

What I have tried:

SQL

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 as OT,
    CASE when   EmployeeDetails.OTEntitled = 'Yes' 
     AND EmployeesAttendance .Hours >= EmployeeDetails.Dhour 
THEN (( EmployeesAttendance.Hours - 8) * 100) else 0 END AS OTAmount,  

   
    Convert(varchar(10), EmployeesAttendance .INTIME,108) as Time,
   Case When Convert(Time, EmployeesAttendance .INTIME,108) > cte.LTime Then 1 else 0 end as Late    
    from EmployeeDetails 
		Left Join EmployeesAttendance ON EmployeeDetails.EmpId = EmployeesAttendance.EmpID
    order by EmployeeDetails.EmpID asc

Posted 9-Jan-19 20:48pm

akhter86

Updated 9-Jan-19 22:28pm

Maciej Los

v2

Add a Solution

2 solutions

Solution 1

Take a deep look at your CASE ... WHEN condition:

SQL

CASE WHEN  EmployeesAttendance.Hours >= 8 THEN 1
 ...
    WHEN  EmployeesAttendance.Hours >= 6 THEN 0.5  Day,

Seems, you should replace [>] with [<] in a second condition.

Posted 9-Jan-19 22:22pm

Maciej Los

Comments

MadMyche 10-Jan-19 4:32am

Good catch, but it actually should work albeit possibly not as intended. CASE follows the first path it matches and ignores the rest.

akhter86 10-Jan-19 4:38am

my this query is working fine on select but not working on update?

Maciej Los 10-Jan-19 4:47am

What's your database[^]?

akhter86 10-Jan-19 5:08am

sql server 2008 r2

Maciej Los 10-Jan-19 5:15am

So, follow the solution 2

akhter86 10-Jan-19 5:34am

which solution

Maciej Los 10-Jan-19 5:40am

by MadMyche

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2019-01-09T22:28:00

The general format for doing an UPDATE based on a JOIN similar to doing a SELECT; I have used this format with both INNER and OUTER joins.

SQL

UPDATE d
SET    d.DestValue1 = s.SourceValue1
FROM   DestinationTable d
JOIN   SourceTable      s ON d.TableIndex = s.TableIndex

It will be up to you to get your table mappings straight

Update
As Maciej Los pointed out in the other reply, you do have some issues with your syntax that should be taken care of

SQL

CASE
    WHEN  EmployeesAttendance.Hours >= 8 THEN 1
    WHEN  EmployeesAttendance.Hours = 0 THEN 0
    WHEN  EmployeesAttendance.Hours >= 6 THEN 0.5  Day, -- error here

CASE
    WHEN  EmployeesAttendance.Hours > EmployeeDetails.Dhour then  EmployeesAttendance .Hours - EmployeesAttendance .Dhour 
    else 0
End as OT,

There is no END to the first statement.
The results will be null between 0 and 6 hours.
I also generally recommend against using Reserved/Special Words as column names; if they must be used then they should be wrapped with [] (eg days, hours). My rule of thumb is if a column or variable name changes color in either C# or TSQL, change the name.

I ran the following statements and you can see how this works out with some real data

SQL

declare @EmployeeAttendance table(id int identity(1,1), [hours] decimal(9,2) )

insert @EmployeeAttendance values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)

SELECT ID, [HOURS],
  CASE
    WHEN  EmployeesAttendance.[Hours] >= 8 THEN 1
    WHEN  EmployeesAttendance.[Hours]  = 0 THEN 0
    WHEN  EmployeesAttendance.[Hours]  >= 6 THEN 0.5
END AS [Day]
FROM @EmployeeAttendance EmployeesAttendance
ORDER BY 1

Which returned the following

ID	HOURS	Day
1	0.00	0.0
2	1.00	NULL
3	2.00	NULL
4	3.00	NULL
5	4.00	NULL
6	5.00	NULL
7	6.00	0.5
8	7.00	0.5
9	8.00	1.0
10	9.00	1.0
11	10.00	1.0

So it looks like you still have some work to do