Declare @Tab Table(EMPID int,EMPNAME varchar(20),logtime datetime,type char(5))
insert @Tab
Select 8,'Mohan Varma','2013-10-30 09:26:00.000','IN'
Union All Select 8,'Mohan Varma','2013-10-30 09:26:00.000','OUT'
Union All Select 8,'Mohan Varma','2013-10-30 09:51:00.000','IN'
Union All Select 8,'Mohan Varma','2013-10-30 10:08:00.000','OUT'
Union All Select 8,'Mohan Varma','2013-10-30 11:48:00.000','IN'
Union All Select 8,'Mohan Varma','2013-10-30 11:58:00.000','OUT'
Union All Select 8,'Mohan Varma','2013-10-30 12:15:00.000','IN'
Union All Select 8,'Mohan Varma','2013-10-30 12:48:00.000','OUT'
Union All Select 8,'Mohan Varma','2013-10-30 13:06:00.000','IN'
Union All Select 8,'Mohan Varma','2013-10-30 13:19:00.000','OUT'
Union All Select 8,'Mohan Varma','2013-10-30 14:03:00.000','IN'
Union All Select 8,'Mohan Varma','2013-10-30 15:40:00.000','OUT'
Union All Select 8,'Mohan Varma','2013-10-30 15:42:00.000','IN'
Union All Select 8,'Mohan Varma','2013-10-30 16:27:00.000','OUT'
Union All Select 8,'Mohan Varma','2013-10-30 16:35:00.000','IN'
Union All Select 8,'Mohan Varma','2013-10-30 18:26:00.000','OUT'
Union All Select 8,'Mohan Varma','2013-10-30 18:30:00.000','IN'
Union All Select 8,'Mohan Varma','2013-10-30 18:35:00.000','OUT'
Select EmpId,EmpName,LogTime,Rn = ROW_NUMBER() OVER (Order By LogTime) Into #Te1 From @Tab Where type = 'In'
Select EmpId,EmpName,LogTime,Rn = ROW_NUMBER() OVER (Order By LogTime) Into #Te2 From @Tab Where type = 'Out'
Select * From #Te1
Select * From #Te2
Declare @Min int
Set @Min = (Select Sum(DATEDIFF(Minute,TE1.LogTime,TE2.LogTime)) AS Minutes From #te1 AS TE1
inner Join #te2 AS TE2
On TE1.rn = TE2.rn)
SELECT CAST( @Min/60 AS VARCHAR(5))+ ' Hrs' + ':'+ RIGHT('0' + CAST( @Min%60 AS VARCHAR(2)), 2)+' Min' AS 'WorkingTime'
Drop Table #TE1
Drop Table #TE2