Hi,
Following code is working fine
SELECT EmpId ,CAST(InTime AS DATE) as workingday ,EmpName,RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2) AS [total hour(s)],
convert(varchar(5), max(OutTime)-MIN(InTime),108) as Emp_Intime,dstatus=
(CASE
WHEN CONVERT(TIME,MIN(InTime),108)>'08:35' and CONVERT(TIME,MIN(InTime),108) <'11:00'
THEN 'L'
WHEN CONVERT(TIME,MIN(InTime),108)>'11:00'
THEN 'halfday'
else 'right' end ),CAST( RIGHT('0' + CONVERT(varchar, FLOOR(SUM( DATEDIFF( MI, InTime, OutTime)/60.0))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM( DATEDIFF( MI, InTime, OutTime))%60), 2)-CAST( max(OutTime)-MIN(InTime) AS DATETIME) AS TIME) Extratime
FROM Baiju.dbo.HbaEmp
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)
empid workingday Empname total_hour(s) Emp_Intime status Extratime
2500 2014-01-01 Arunkumar 10:10 09:40 L 00:30:00.0000000
2502 2014-01-01 Arsh 07:48 06:48 halfday 01:00:00.0000000
2503 2014-01-01 RaJkumar 08:40 08:10 L 00:30:00.0000000
2504 2014-01-01 hari NULL NULL right NULL
2504 2014-01-01 Sini 08:55 08:55 L 00:00:00.0000000
my requirement is to find overtime.
add a field Overtime .and condition is (Emp_Intime-00:45)-Extratime and check if the value is above 8 hour the overtime is value-8 else overtime is 0
for example first record of above should be
empid workingday Empname total_hour(s) Emp_Intime status Extratime overtime
2500 2014-01-01 Arunkumar 10:10 09:40 L 00:30:00.0000000 25
ie (09:40-45)-30.
ie 08:55-30=08:25
overtime is 25 minutes
How it is possible
Regards
Baiju