Calculate time difference between two times in mysql

Question

0.00/5 (No votes)

See more:

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

C++

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

SQL

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

Posted 10-Oct-16 19:34pm

Merajuddin Ansari

Updated 14-Oct-16 22:56pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Suvendu Shekhar Giri · Accepted Answer · 2016-10-11T09:15:00

Solution 1

I have seen people achieving this kind of result easily using APPLY or CTE (Common Table Expression).
It is also possible to do this using temporary table and cursor etc but I using APPLY or CTE should be far easier removing chances of mistakes and all those complexities.

Please check following links if they can give a brief idea of what I am referring to.
sql - Calculate time difference for attendance - Stack Overflow[^]
sql server - How can I calculate time spent at work in a single column? - Database Administrators Stack Exchange[^]
Converting sequential time records into IN and OUT times[^]

Hope, these helps :)
Please let me know if I am missing something obvious here.

Posted 11-Oct-16 9:15am

Suvendu Shekhar Giri

Comments

Merajuddin Ansari 12-Oct-16 1:43am

what about to use Cursor.?

Suvendu Shekhar Giri 12-Oct-16 2:40am

You can use it but it should be the last choice, I think. That's because CURSOR will be much slower compared to other approaches.

Merajuddin Ansari 12-Oct-16 3:47am

i have done something like this.is this ok.?

declare @day_att table (UserID INT,signtime DATETIME,status CHAR(1))
declare @hrs table (_hours float)
insert into @day_att(UserID,signtime,status) select empno,signtime,status from EmpAtt where empno = 248 and atdate = '2016-09-27' order by signtime

insert into @hrs(_hours) select CAST(DATEDIFF(MINUTE,I.signtime,O.signtime)/60.0 AS float) [Hours]
FROM @day_att I OUTER APPLY (SELECT TOP 1 signtime,status FROM @day_att t WHERE t.UserID = I.UserID AND t.signtime > I.signtime ORDER BY t.signtime) O
WHERE I.status = 'I' AND O.status = 'O'

select sum(_hours) as totalHrs from @hrs

Merajuddin Ansari · Accepted Answer · 2016-10-14T22:56:00

SQL

declare @day_att table (UserID INT,signtime DATETIME,status CHAR(1))
declare @hrs table (_hours float)
insert into @day_att(UserID,signtime,status) select empno,signtime,status from EmpAtt where empno = 248 and atdate = '2016-09-27' order by signtime

insert into @hrs(_hours) select CAST(DATEDIFF(MINUTE,I.signtime,O.signtime)/60.0 AS float) [Hours]
FROM @day_att I OUTER APPLY (SELECT TOP 1 signtime,status FROM @day_att t WHERE t.UserID = I.UserID AND t.signtime > I.signtime ORDER BY t.signtime) O
WHERE I.status = 'I' AND O.status = 'O'

select sum(_hours) as totalHrs from @hrs