Forums - C# Corner

Forum guidelines
Akhter HUssain

Akhter HUssain

  • 1.5k
  • 294
  • 4.1k

case when condition check for overtime allow in employee.

Jan 6 2019 1:43 AM
i want that if employee is allowed to overtime amount, then that employee overtime amount will be calculated else 0.Overtime allowed check is defined in employee table
 
here is my sql query of CTE
 
;With CTE As
(
select EmployeeDetails.EmpID,EmployeeDetails.EmpName,EmployeeDetails.OTEntitled from EmployeeDetails
)
,CTE4 As
(
Select MachineAttendance.EmpID,MachineAttendance.Datetime,MachineAttendance.INOUT from MachineAttendance
)
,cte1 AS
(
SELECT CTE4.EmpID, CAST(CTE4.Datetime as Date) AS [Date],
CASE WHEN CTE4.INOUT = 1 THEN CTE4.DateTime END AS INOUT_INTIME,
CASE WHEN CTE4.INOUT = 2 THEN CTE4.DateTime END AS INOUT_OUTTIME
From
CTE4
),
cte2
as
(
select cte1.EmpID, Date, MAX(INOUT_INTIME) AS INTIME,
MAX(INOUT_OUTTIME) AS OUTTIME
, DATEDIFF(Hour, MAX(INOUT_INTIME), MAX(INOUT_OUTTIME)) as [Hours]
FROM CTE1
GROUP BY EmpID, [Date]
)
select cte.EmpName, cte2.EmpID,cte.OTEntitled, cte2.INTIME, cte2.OUTTIME, cte2.[Hours]
, CASE WHEN cte2.[Hours] >= 8 THEN 1
WHEN cte2.[Hours] = 0 THEN 0
WHEN cte2.[Hours] >= 6 THEN 0.5 END AS [Day],
CASE WHEN cte2.[Hours] > 8 then cte2.[Hours] - 8 else 0 End as OT,
CASE when cte.OTEntitled = 'Yes'
cte2.[Hours] >= 8
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) > '09:10' Then 1 else 0 end as Late
from cte2
Right outer Join cte ON cte.EmpId= cte2.EmpID

Answers (1)