在mysql中计算两次之间的时差

问题描述

大家好我需要你的帮助。


每当有两个连续的I（IN）和O（OUT）时，
i需要计算时差
$列status中的b $ b然后必须添加连续I（IN）和O（OUT）的所有时差



注意： - 我（IN）在公司签到时间，O（OUT）退出公司的时间

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

推荐答案

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

这篇关于在mysql中计算两次之间的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！