需要计算加班费的总和

我有一个表格TEST

AccountName AccountIndex    AccountID   StartTime   EndTime     checkouttime                
ABC             3             7     07:00:00.00 16:00:00.00 2016-07-22 17:03:00     
ABC             3             7     07:00:00.00 16:00:00.00 2016-07-23 16:00:00     
ABC             3             7     07:00:00.00 16:00:00.00 2016-07-25 17:04:00

我必须计算加班费的总和。

我正在试用这个

select name,accountid,case when (cast(CheckOutTime as time) < EndTime) then '-' else '' end + 
 convert(varchar(8), 
  dateadd(minute, 
   abs(
    DATEDIFF(minute, 
     cast(CheckOutTime as time)
    , EndTime)
   )
 ,0)
,108) as Overtime
from test

我得到了作为的o/p

name    accountid   Overtime    
ABC     7         01:03:00  
ABC     7         00:00:00  
ABC     7         01:04:00

我想要像一样的o/p

name    accountid   Overtime    
ABC         7       02:07:00

加班费总额如何实现

   select accountid,name,cast((totalseconds/3600) as varchar) + ':' + cast(((totalseconds%3600)/60) as varchar) as overtime
from
(
 select accountid,name,
           sum(Datediff(s,Endtime,cast(checkouttime as time))) as totalseconds
    group by accountid,name
    ) t

使用以上查询计算加班

Declare @YourTable table (AccountName varchar(50),AccountIndex int,AccountID int,StartTime varchar(25),EndTime varchar(25), checkouttime datetime )
Insert into @YourTable values 
('ABC',3,7,'07:00:00.00','16:00:00.00','2016-07-22 17:03:00'),
('ABC',3,7,'07:00:00.00','16:00:00.00','2016-07-23 16:00:00'),     
('ABC',3,7,'07:00:00.00','16:00:00.00','2016-07-25 17:04:00')
Select AccountName
      ,AccountID 
      ,OverTime = cast(DateAdd(MINUTE,sum(DateDiff(MINUTE,cast(EndTime as time),case when cast(EndTime as time)>cast(checkouttime as time) then cast(EndTime as time) else cast(checkouttime as time) end)),'00:00:00') as time)
 From @YourTable      
 Group By AccountName,AccountID

AccountName AccountID   OverTime
ABC         7           02:07:00.0000000

要获得更易读的文本，可以使用CTE

;with cteBase as (
    Select AccountName
          ,AccountID
          ,EndTime     =cast(EndTime as time)
          ,checkouttime=cast(checkouttime as time)
     From  @YourTable
)
Select AccountName
      ,AccountID 
      ,OverTime = cast(DateAdd(MINUTE,sum(DateDiff(MINUTE,EndTime,case when EndTime>checkouttime then EndTime else checkouttime end)),'00:00:00') as time)
 From cteBase      
 Group By AccountName,AccountID

SUM不适用于时间数据类型。

您可以按名称、帐户ID对数据进行分组并使用

SUM(DATEDIFF(ss, '00:00:00.000', [your overtime value]))

找到总秒数，最后用DATEADD((函数将秒数转换回时间。

更新：

select 
    acc_id, 
    name, 
    CONVERT(VARCHAR(20), DATEADD(ss, SUM(DATEDIFF(ss, '00:00:00.000', [your overtime value])), '00:00:00'), 114) as overtime
from [your table]
group by acc_id, name

如果您预计一个acc_id的加班时间可能超过24小时，那么您可以添加另一列来显示天数(秒数/(3600*24((

它也会满足您的要求，所以请也尝试一下：(它只适用于2012年(

SELECT AccountName, AccountID, 
    CONCAT(t.tot/60, ':', t.tot%60,':00') orverTime 
FROM (
    SELECT AccountName, AccountID, 
    SUM(DATEDIFF(mi, CONCAT(SUBSTRING(
    CAST(checkouttime AS VARCHAR(50)), 1, 11), ' ', EndTime),
    checkouttime)) tot
FROM #tmp
GROUP BY AccountName, AccountID ) t

相关内容

最新更新

热门标签：