-3

below query is just updating INTIME and OUTTIME and rest of case when condition is not getting calculating as per case when condition.

Update EmployeesAttendance 
set  EmployeesAttendance.INTIME=@INTime,
EmployeesAttendance.OUTTIME=@OUTTIME,
EmployeesAttendance.Hours= ( (DATEDIFF(Hour,OUTTIME,INTIME))),  
EmployeesAttendance.Days =(  CASE WHEN  EmployeesAttendance.Hours >= 
EmployeeDetails.Dhour 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 - EmployeeDetails.Dhour) * 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.AttIDS=@AttIDS
  • 3
    Hi, welcome to DBA.SE, could you add as much information as possible, such as the table definition, the expected result, the SQL Server version, ... . Doing that makes it easier for us, and faster for you to get the solution you want. – Randi Vertongen Jan 10 at 18:36
  • @RandiVertongen i have two table one is employee Details and second one is employee attendance ,in Employee attendance i am updating INTIME and OUTTIME ,then i am taking difference INTIME and OUTIME which is hours,then Calculating Day in Employee attendance from standard duty time which is defined in employee details table ... – akhter Hussain Jan 11 at 2:08
0

Try running the update like this:

I could not test because the table definitions were not provided, but generally when updating from this is the syntax:

 Update EA
    set  EA.INTIME=@INTime,
    EA.OUTTIME=@OUTTIME,
    EA.Hours= ( (DATEDIFF(Hour,OUTTIME,INTIME))),  
    EA.Days =(  CASE WHEN  EA.Hours >= 
    ED.Dhour THEN 1
    WHEN  EA.Hours = 0 THEN 0
    WHEN  EA.Hours >= 6 THEN 0.5 end),

    EA.OT=(CASE WHEN  EA.Hours > 
     ED.Dhour then  
     EA.Hours - ED.Dhour else 0 End),

     EA.OTAmount =(CASE when ED.OTEntitled = 'Yes' 
     AND EmployeesAttendance .Hours >= ED.Dhour 
     THEN (( EA.Hours - ED.Dhour) * 100) else 0 
     END ),   

     EA.Time=( 
     Convert(varchar(10),EA.INTIME,108)),
     EA.Late=(Case When Convert(Time, EmployeesAttendance 
    .INTIME,108) > ED.LTime Then 1 else 0
    end)  

    From  EmployeesAttendance EA
    INNER Join EmployeeDetail ED 
            ON ED.EmpId = EA.EmpID
    --  Order by ED.EmpID asc
    where EA.AttIDS=@AttIDS

from this link there is the code below, that you can take as an example:

SQL Server: Update From Select Statement

use tempdb
go

/* Creating First table */
CREATE TABLE [dbo].[MyTecBits_Table_1](
[Sl_no] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Description] [varchar](500) NULL
) ON [PRIMARY]
GO

insert into MyTecBits_Table_1 values ('Name One','Description One')
insert into MyTecBits_Table_1 values ('Name Two','Description Two')
insert into MyTecBits_Table_1 values ('Name Three','Description Three')
insert into MyTecBits_Table_1 values ('Name Four','Description Four')

/* Creating Second table */
CREATE TABLE [dbo].[MyTecBits_Table_2](
[Sl_no] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Description] [varchar](500) NULL
) ON [PRIMARY]
GO

insert into MyTecBits_Table_2 values ('1st Name','1st Description')
insert into MyTecBits_Table_2 values ('2nd Name','2nd Description')
insert into MyTecBits_Table_2 values ('3rd Name','3rd Description')
insert into MyTecBits_Table_2 values ('4th Name','3rd Description')

--before the update
select *
From MyTecBits_Table_2 as b Inner Join MyTecBits_Table_1 as a 
    On a.Sl_no = b.Sl_no

    --have a look at the update here
Update MyTecBits_Table_1 
Set Name = b.Name, Description = b.Description
From MyTecBits_Table_2 as b Inner Join MyTecBits_Table_1 as a 
    On a.Sl_no = b.Sl_no

--after the update
select *
From MyTecBits_Table_2 as b Inner Join MyTecBits_Table_1 as a 
    On a.Sl_no = b.Sl_no

enter image description here

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.