kl baiju

kl baiju

  • NA
  • 53
  • 0

how to find overtime from sql query

Feb 18 2014 4:06 AM

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