如何使用sqlserver找到加班 [英] how to find overtime using sqlserver

问题描述

以下代码工作正常

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

我的要求是找到加班费。



添加一个字段加班。条件是（Emp_Intime-00：45）-Extratime并检查值是否超过8小时加班值是否为值8否则加班时间为0



例如上面的第一条记录应该是

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



加班费是25分钟



怎么可能



问候



Baiju

ie (09:40-45)-30.

ie 08:55-30=08:25

overtime is 25 minutes

How it is possible

Regards

Baiju

推荐答案

通常一个组织的工作时间是每天8小时，所以我用工作时间为8小时，从总小时数中扣除，以便显示加班费。

Normally working hours of an organisation is 8 hrs per day so I have used working hrs as 8 and deduct from total hours so it shows Overtime.

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), '08:00',108) as Working_hours,dstatus=
    (CASE
     WHEN CONVERT(TIME,MIN(InTime),108)>'08:35' and CONVERT(TIME,MIN(InTime),108) <'11:00'
     THEN 'Late'
      WHEN CONVERT(TIME,MIN(InTime),108)>'11:00'
      THEN 'Halfday'
      WHEN CONVERT(TIME,MIN(OutTime),108)>'16:30'
      THEN 'OT'
      else 'Full Day' 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( '08:00' AS DATETIME) AS TIME) Extratime
    FROM HbaEmp
    GROUP BY EmpId , EmpName, CAST(InTime AS DATE)

1   2014-02-21  AAA 09:00   08:00   OT  01:00:00.0000000
2   2014-02-21  BBB 08:00   08:00   Full Day    00:00:00.0000000
3   2014-02-21  CCC 08:35   08:00   OT  00:35:00.0000000

这篇关于如何使用sqlserver找到加班的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！