getting total time in sql

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