# 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

## Answers

```   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

```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
```

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))

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
```

### BAD_ACCESS on very low number of users during getaddrinfo

Okay, I am completely at a loss here.

### C# Convert Unicode chars

is it possible to convert a string like "\u00e8" (Got it by reading a WebRequestResponse with Streamreader) to it's unicode char(è)?