Click here to Skip to main content
15,949,686 members
Search within:


Store procedure , case when condition is not executing
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
 
I amupdating INTIme and OUTTime in a table of Employee attendance using Update store Procedure,INTIME and OUTTIME is getting updated in a table ,but further query for calculating Hours and OT,OTAmount and Late which is base on case when condition is not working .


What I have tried:

SQL
<pre>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
end
Posted
Updated 10-Jan-19 8:19am
Add a Solution
Comments
MadMyche 10-Jan-19 13:18pm    
First thing I'd do is use aliases for the table names and then format your code, such as has been shown in the answers to your other questions.

1 solution

There are a few things that would help you.

  • Use CTE to query the data to update from and use that table in the UPDATE statement. In other words, build a query that returns the data you're about to use as source data. Something like the following pseudo example
    SQL
    ;WITH SourceQuery (OutTime, InTime, Hours, Days)
  SELECT ....
  FROM EmployeesAttendance ea, EmployeeDetails ed
  WHERE ...
)
UPDATE ea
    SET Hours = sq.Hours, ...
FROM EmployeesAttendance ea, SourceQuery  sq
WHERE ...

    This kind of structure makes it easy to see what are values that are going to be inputted into the SET clause of an UPDATE statement



  • Hours column, why calculate it into the table. It would be much more reliable and easy if you use a Computed Column[^] Something like
    SQL
    ALTER TABLE EmployeesAttendance 
   ADD Hours AS DATEDIFF(Hour, OUTTIME, INTIME)



  • Values during the update execution, If I understand your intentions correctly you're trying to use intermediate results from calculations in your statement. Looking at the following part
    SQL
    ...
EmployeesAttendance.Hours= ( (DATEDIFF(Hour,OUTTIME,INTIME))),  
  EmployeesAttendance.Days =(  CASE WHEN  EmployeesAttendance.Hours >= EmployeeDetails.Dhour THEN 1
...

    When the statement is executed, the column EmployeesAttendance.Hours contains the original value until the statement is finished. Even though you calculate the Hours in previous part, during the execution, the column values are original values, not intermediate results. I think this is the cause why you say that the CASE structure isn't working



  • The same thing applies to variables. Looking at the following part
    SQL
    ...
set  EmployeesAttendance.INTIME=@INTime,
     EmployeesAttendance.OUTTIME=@OUTTIME,
     EmployeesAttendance.Hours= ( (DATEDIFF(Hour,OUTTIME,INTIME))),
...

    Even though you have assigned the values from the variables, the calculation for column Hours will be done using the original values from the record, not using the newly assigned values. So if you would want to use the new values, you should write
    SQL
    ...
set  EmployeesAttendance.INTIME=@INTime,
     EmployeesAttendance.OUTTIME=@OUTTIME,
     EmployeesAttendance.Hours= ( (DATEDIFF(Hour,@OUTTIME,@INTime))),
...

    But again, using a CTE as a basis to the update operation would simplify the situation.
 
Share this answer
 
Posted
Comments
Member 12314309 10-Jan-19 21:29pm    
Wndelius ,i used cte but it just input INTIME and OUTTIME and nothing else doing...get much confuse about this ,,,pleas review below code SP

ALTER PROCEDURE [dbo].[GetMachineAttendanceFinal7]
@INTIME Datetime,
@OUTTIME Datetime,
@AttIDS int
AS

BEGIN

SET NOCOUNT ON;


--;WITH Dates (ReportingDate)
--AS (
-- SELECT CONVERT(date, '2018-11-26 00:00:00.000', 120) AS ReportingDate
-- UNION ALL
-- SELECT DATEADD(day, 1, d.ReportingDate)
-- FROM Dates d
-- WHERE d.ReportingDate < CONVERT(date, '2018-12-25 00:00:00.000', 120))

;WITH CTE As
(
select EmployeeDetails.EmpID,EmployeeDetails.EmpName,EmployeeDetails.OTEntitled,EmployeeDetails.Empcur,EmployeeDetails.Dhour,EmployeeDetails.LTime from EmployeeDetails where Empcur='Join'
),
EAtt
As
(
Select EmployeesAttendance.AttIDS,EmployeesAttendance.ReportingDate,EmployeesAttendance.Date,EmployeesAttendance.Days,EmployeesAttendance.EmpID,EmployeesAttendance.Hours,EmployeesAttendance.INTIME,EmployeesAttendance.OUTTIME,EmployeesAttendance.Late,EmployeesAttendance.Time,EmployeesAttendance.OTAmount From EmployeesAttendance
)
,cte2
as

(
Select EAtt.AttIDS,EATT.ReportingDate,EATT.Date,EATT.Days,EATT.EmpID,
EATT.Hours,EATT.INTIME,EATT.OUTTIME,EATT.Late,EATT.Time,EATT.OTAmount

FROM EATT

),
cteFinal AS (
select cte.EmpID,cte2.ReportingDate,cte2.Date,cte2.INTIME,cte2.OUTTIME,

DATEDIFF(Hour, @INTIME, @OUTTIME) as [Hours]
, CASE WHEN cte2.[Hours] >= 8 THEN 1
WHEN cte2.[Hours] = 0 THEN 0
WHEN cte2.[Hours] >= 6 THEN 0.5 END AS [Days],
CASE WHEN cte2.[Hours] > CTE.Dhour then cte2.[Hours] - CTE.Dhour else 0 End as OT,
CASE when
cte.OTEntitled = 'Yes' AND cte2.[Hours] >= CTE.Dhour
THEN (( cte2.[Hours] - 8) * 100) else 0 END AS OTAMount,
Convert(varchar(10), cte2.INTIME,108) as [Time],
Case When Convert(Time, cte2.INTIME,108) > cte.LTime Then 1 else 0 end as Late
from cte
Left Join cte2 ON cte2.EmpId= cte.EmpID
)
UPDATE cte2
SET cte2.INTIME = @INTIME,
cte2.OUTTIME=@OUTTIME,
cte2.Hours=Hours
where cte2.AttIDS=@AttIDS
Wendelius 10-Jan-19 23:27pm    
In this code, the problem is that you try to update the cte2. Instead you should use the cte2 query for the values to update another table

Consider the following
WITH cte2 (.....)
UPDATE ea
SET InTime = c.InTime,
    Hours = c.Hours,
    ...
FROM EmployeeAttendance ea, cte2 c
WHERE ea.EmpID = c.EmpID
Member 12314309 10-Jan-19 22:09pm    
Thanks Wendelius .....Too much for opening my mind for thinking out of the box i have done it thanks again
Wendelius 10-Jan-19 23:28pm    
You're welcome!
Add a Solution

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise
Privacy
Cookies
Terms of Use
Last Updated 10 Jan 2019
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web04 2.8:2024-07-22:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900