Calculate time difference between two times in mysql

Question

Hi guys i need your help.

i need to calculate time difference whenever there are two consecutive I (IN) and O (OUT)
in the column "status" and then have to add all time differences of consecutive I (IN) and O (OUT)

NOTE:- where I (IN) is sign in time in the company and O (OUT) is sign out time from the company

 

emp_no   attandence_date                      sign_time                               status

---------------------------------------------------------------------------------------------

248         2016-09-27 00:00:00.000        1900-01-01 07:40:59.000            I

248         2016-09-27 00:00:00.000        1900-01-01 12:07:54.000           I

248         2016-09-27 00:00:00.000        1900-01-01 14:48:20.000           O

248         2016-09-27 00:00:00.000        1900-01-01 14:48:38.000           I

248         2016-09-27 00:00:00.000        1900-01-01 14:50:24.000           O

248         2016-09-27 00:00:00.000        1900-01-01 15:04:08.000           I

248         2016-09-27 00:00:00.000        1900-01-01 15:33:04.000          O

---------------------------------------------------------------------------------------------

 

 

What I have tried:

this is the function i have created but i am searching some simple way to do this

 

 

ALTER function [dbo].[fn_consumed_hours_inComp]

(

@emp_id varchar(10),@attDate varchar(12) ,@in1_r varchar(5),@Out1_r varchar(5), @flag bit

)

returns varchar(15)

as

begin

declare @hours int, @totalMinutes int, @minutes int, @counter int, @maxid int, @in1 datetime, @out1 datetime,@status_i char(1),@status_o char(1),

@hrs int, @mins int, @result varchar(15),@hr char(3), @mn char(3)

if @flag='True' --calculate total hrs in company

begin

declare @day_att table (loop_id int identity(1,1),in1 datetime, status char(1))

insert into @day_att(in1,status) select signtime,status from EmpAtt where empno = @emp_id and atdate = @attDate order by signtime

select @maxid = count(loop_id) from @day_att

set @counter = 1

set @totalMinutes=0

while @counter <= @maxid

begin

select @status_i=status from @day_att where loop_id=@counter

select @status_o=status from @day_att where loop_id=@counter+1

if @status_i = 'I' and @status_o ='O'

begin

select @in1=in1 from @day_att where loop_id=@counter

select @out1=in1 from @day_att where loop_id=@counter+1

select @minutes=DATEDIFF(MINUTE,@in1,@out1)

set @totalMinutes=@totalMinutes+@minutes

set @counter = @counter + 2

end

else

set @counter = @counter + 1

end

select @hrs = @totalMinutes / 60

select @mins = @totalMinutes % 60

select @result = cast(@hrs as varchar(10)) + ':' + cast(@mins as varchar(10))

end

return @result

end