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