Rate this:
See more:
I have problem with calculating the total working time in h:m:s format

pls help.

this is my sample data

```empid time        date         status
1     9:30:45     08-01-2013   logOut
1     12:30:59    08-01-2013   logOut```
Posted 7-Jan-13 16:11pm
Edited 7-Jan-13 16:29pm
v2

Rate this:

## Solution 3

Hi Manoj

As per my understanding you want this .....

I create a table like you mention(add a primary key ) and fill the same data .....

```Create table T1
(ID int identity (1,1),
EmpID int ,
Etime time,
EDate date,
EStatus Char(1) )```
Write a sql query
```SELECT Tab1.EMPID , Tab1.EDate
,SUM([SECONDS]) AS [TOTAL TIME in SECONDS]
,CONVERT(varchar (20), SUM(Tab1.[SECONDS])/(60 * 60))+ ':' +
CONVERT(varchar(20), (SUM(Tab1.[SECONDS])%(60 * 60))/ 60) + ':' +
CONVERT(varchar(20), (SUM(Tab1.[SECONDS])%(60 * 60*60))%60) AS [WORKING TIME]
FROM
(SELECT A.EmpID , A.Etime AS [IN TIME]
, B.Etime AS [OUT TIME]
, A.EDate
, Datediff (SECOND,A.Etime,B.Etime  ) as [SECONDS]
FROM
T1 as A (NOLOCK)
INNER JOIN t1 as B  ON a.ID = (b.ID -1)  AND A.EStatus = 'I'
WHERE  B.EStatus = 'O' ) AS Tab1
GROUP BY Tab1.EMPID , Tab1.EDate
```

Output
```EMPID       EDate      TOTAL TIME in SECONDS WORKING TIME
----------- ---------- --------------------- --------------------------------------
1           2013-08-01  11780                 3:16:20

----------------------------------------------------------------------------------
```
Regards,
Vijay
manoj12.shrivastava at 8-Jan-13 10:54am

Thank you very much sir
Rate this:

## Solution 1

What have you tried ? DateDiff will give you the difference between two dates.
Rate this:

## Solution 2

you can refer version 2, in below link

Get duration between two dates in years,months,days and hours using sql server[^]

Happy Coding!
URVISHSUTHAR at 8-Jan-13 3:27am

yep, it can fulfill your recruitment