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 .
<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
UPDATE
;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 ...
SET
ALTER TABLE EmployeesAttendance ADD Hours AS DATEDIFF(Hour, OUTTIME, INTIME)
... EmployeesAttendance.Hours= ( (DATEDIFF(Hour,OUTTIME,INTIME))), EmployeesAttendance.Days =( CASE WHEN EmployeesAttendance.Hours >= EmployeeDetails.Dhour THEN 1 ...
EmployeesAttendance.Hours
Hours
CASE
... set EmployeesAttendance.INTIME=@INTime, EmployeesAttendance.OUTTIME=@OUTTIME, EmployeesAttendance.Hours= ( (DATEDIFF(Hour,OUTTIME,INTIME))), ...
... set EmployeesAttendance.INTIME=@INTime, EmployeesAttendance.OUTTIME=@OUTTIME, EmployeesAttendance.Hours= ( (DATEDIFF(Hour,@OUTTIME,@INTime))), ...
WITH cte2 (.....) UPDATE ea SET InTime = c.InTime, Hours = c.Hours, ... FROM EmployeeAttendance ea, cte2 c WHERE ea.EmpID = c.EmpID
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)