Click here to Skip to main content
14,423,257 members
Rate this:
Please Sign up or sign in to vote.
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:

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
Updated 9-Jan-19 23:28pm
v2
Rate this:
Please Sign up or sign in to vote.

Solution 2

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.
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
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
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
   
v2
Comments
akhter86 10-Jan-19 5:12am
   
MadMyche

i have developed SP,it just updating INTTIME and OUTIME rest of column values are not getting calculate....Please review my code

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
INNER Join EmployeesAttendance ON EmployeeDetails.EmpId = EmployeesAttendance.EmpID
-- Order by EmployeeDetails.EmpID asc
where EmployeesAttendance.AttdID=@AttdID
Maciej Los 10-Jan-19 5:15am
   
5ed!
Rate this:
Please Sign up or sign in to vote.

Solution 1

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

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


Seems, you should replace [>] with [<] in a second condition.
   
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

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100