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
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
Merajuddin AnsariPosted Oct 15, 2016, 4:59 AM
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
Nitin SontakkePosted Oct 12, 2016, 1:43 AM
Merajuddin AnsariPosted Oct 12, 2016, 1:35 AM
Nitin SontakkePosted Oct 12, 2016, 12:30 AM