case when condition check for overtime allow in employee.

Question

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