CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Red Devil
Red Devil

Reputation: 2403

Need to calculate sum of overtime

I have a table 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

I have to calculate the sum of overtime.

I am trying this

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

And i am getting the o/p as

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

I want to have the o/p like

name    accountid   Overtime    
ABC         7       02:07:00

sum of overtime how to achieve that

Upvotes: 0

Views: 200

Answers (4)

Shushil Bohara
Shushil Bohara

Reputation: 5656

It will also fulfill your requirement so please try with this as well: (It only works on 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

Upvotes: 0

Akshey Bhat
Akshey Bhat

Reputation: 8545

   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

Use the above query to calculate overtime

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81990

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

Returns 

AccountName AccountID   OverTime
ABC         7           02:07:00.0000000

For a more readable one, you could use a 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

Upvotes: 1

Anton
Anton

Reputation: 2882

SUM does not work against time data type.

you can group the data by name, accountID and use

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

to find the total number of seconds, and finally convert the number of seconds back to time with DATEADD() function.

UPDATE:

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

If you expect the overtime may be more than 24 hours for one acc_id, then you can add another column to show the number of days (number of seconds / (3600 * 24))

Upvotes: 0

Related Questions