Red Devil Red Devil - 2 months ago 7
SQL Question

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

Answer
   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