T-sql calculate the exact number of minutes for employee based on his schedule

Question

Hello all

I have a table that contains the employees time in time out



And another table contains the schedule for each employee per day; an employee could have more than one shift per day

Employee Id
Date
Start time
End time


I am trying to write a query that could calculate the exact time (in minutes) for an employee but the criteria is

Suppose that the start time for the employee EMP-1 is 9:00 and the end time is 15:00
EMP-1 has another shift start @ 18:00 and end @ 22:00

 EMP         Date       TimeIn    TimeOut
EMP-1   7-10-2009    9:00         15:00
EMP-1   7-10-2009    18:00        22:00


If in the table Time in / time out we have

 EMP         Date         TimeIn    TimeOut
EMP-1    7-10-2009     8:44        12:00
EMP-1    7-10-2009     12:30      15:30
EMP-1    7-10-2009     18:30      20:30
EMP-1    7-10-2009     21:30      22:15

The calculation must be: he start @ 8:44 but the starting time in his schedule is 9:00 so we don’t count the minutes between 8:44 and 9:00
He finish @ 15:30 but the end time in his schedule is 15:00 so the 30 minutes plus are not counted
We count 12:00 – 9:00 ==> 180 minutes + (15:00 – 12:30) ==> 150 minutes
So for EMP-1 we must have 180 + 150 = 330.

Also the same calculation for the second shift.

Is that possible to do it , to calculate the number of minutes of an employee based on his exact schedule?

I was thinking to add a field on the timein_timeout table (number of minutes) and to calculate on the fly the number of minutes when he check out, but I had a problem when the employee has more then one shift...

set @Row_Numb = (Select max(Row_Numb)
                                        from timein_timeou
                                        where Employee_Id  = @Employee_Id
                                        and date = @date
if @Row_Numb is null -- mean this is a check in
begin
        
        insert into timein_timeou
        values( @Employee_Id
                        , @date
                        , getdate()
                        , Null
                        , Null )
 
end
else
begin
       
        set @chek_In = (select  Time_In
                                        from timein_timeou
                                        where  Row_Numb=@Row_Numb)
        set @chek_out= (select Time_Out
                                         from timein_timeou
                                         where  Row_Numb=@Row_Numb )
        if @chek_out is null
        begin
-- now if we have one shift so there is no pb, but the pb is if we have more --then one chift :-(
                set @time_in =( select time_in from tbl_schedule where employee_id = @employee_id )
                set @ time_out = select time_out from tbl_schedule where employee_id = @employee_id )
--              ....... SO FROM HERE I HAVE A PB IF WE HAVE MORE THEN ONE
--SHIFT, SHOULD I USE CURSOR ON TABLE SCHEDULE? I DON"T KNOW IF
 --THIS IS A BEST WAY, I AM JUST TRYING TO AVOID CTE...
        end
        else
        begin  
                insert into timein_timeou 
                        values(@Employee_Id
                                        ,@date
                                        , getdate()
                                        , Null
                                        ,Null)
        end
end 

For Example Should I use a cursor on the table schedule to get all the shift o to the calculation in a loop while ??

Can any one help me plz?
Any hint any idea is the most welcome even if I had to change the design of my tables..

Answer 1

It's possible to count the total minutes per day, but how do you know what is the schedule for each employee? Is there a fixed schedule for all employees or it could change per employee?

---

Abdallah, PMP, MCTS

Answer 2

This is the table of schedule per employee
and yes it could change from employee to another and some employees may have two or more than one shift

EmployeeID as nvarchar(10)
StartTime as time
EndTime as time.

Btw I am still in the implementing phase, so I could change the design of table if this help to find a better solution

Answer 3

try this

declare @schedule table ( EMP nvarchar(10),  Date  datetime, TimeIn datetime, TimeOut datetime)
declare @emp table ( EMP nvarchar(10),  Date  datetime, TimeIn datetime, TimeOut datetime)

insert into @schedule 
select 'EMP-1', '7-10-2009', '9:00' , '15:00' union all
select 'EMP-1' , '7-10-2009' , '18:00' , '22:00'

insert into @emp 
 
select 'EMP-1' , '7-10-2009' , '8:44' , '12:00' union all
select 'EMP-1' , '7-10-2009' , '12:30' , '15:30' union all
select 'EMP-1' , '7-10-2009' , '18:30' , '20:30' union all
select 'EMP-1' , '7-10-2009' , '21:30' , '22:15'  

 
select s.date, s.timein 'schedualed timein', s.timeout 'schedualed timeout', sum(datediff(minute, case when e.timein>s.timein then e.timein else s.timein end, case when e.timeout<s.timeout then e.timeout else s.timeout end)) as 'minutes spend' from @schedule s 
	inner join @emp e on e.timein between s.timein and s.timeout
						or  e.timeout  between s.timein and s.timeout
 
 group by s.date, s.timein, s.timeout

Answer 4

This is the table of schedule per employee
and yes it could change from employee to another and some employees may have two or more than one shift

EmployeeID as nvarchar(10)
StartTime as time
EndTime as time.

Btw I am still in the implementing phase, so I could change the design of table if this help to find a better solution

Hello Wael

I suggest you add another table to hold the employees schedules. This is the only way you can compare the time worked to the actual schedule(unless you want to remember the schedule for each employee)

---

Abdallah, PMP, MCTS

Answer 5

Hello Abdshall,

I didn't get your idea, 
in fact i have two table in my database
one to store the employee schedules

Tbl_Schedule
EmployeeID as nvarchar(10)
StartTime as time
EndTime as time.


and other to record the check in check out activity

Tbl_TimeIn_TimeOut
EmployeeID as nvarchar(10)
Date as date
Check_in   as time
Check_Out as time

Answer 6

Hello Abdshall,

I didn't get your idea, 
in fact i have two table in my database
one to store the employee schedules

Tbl_Schedule
EmployeeID as nvarchar(10)
StartTime as time
EndTime as time.


and other to record the check in check out activity

Tbl_TimeIn_TimeOut
EmployeeID as nvarchar(10)
Date as date
Check_in   as time
Check_Out as time

did you look on the solution that i have provided in my previous post ?

Answer 7

Hello Arif
Thank you very for the solution it works

I would like to have your opinion About The query I wrote before, is that better to calculate the number of minutes on the fly?

this just an idea, I am trying to do it with optimal and best way

thank you indeed for your help
 

Answer 8

Hello Abdshall,

I didn't get your idea, 
in fact i have two table in my database
one to store the employee schedules

Tbl_Schedule
EmployeeID as nvarchar(10)
StartTime as time
EndTime as time.


and other to record the check in check out activity

Tbl_TimeIn_TimeOut
EmployeeID as nvarchar(10)
Date as date
Check_in   as time
Check_Out as time

Wael, in Arif's solution you can see what I mean, and apparently that's how you have it in your database.
I didn't know if you had a table to store the schedules becuase you need to actual schedule to compare it with the hours worked.

---

Abdallah, PMP, MCTS

Answer 9

Hi Arif,
well the situation became more complex and I don't know if you could help

in the Employee Schedule

EmpId               Time In          Time Out
1                       12:00             14:00
1                       16:00             18:00

And Activity

EmpId              Check In          Check Out
1                      11:30                 12:30
1                      13:00                 14:30
1                      16:30                 18:30

is there is a way to get the result like this

EmpId               Time In          Time Out     First Check In      Last Check Out      Minutes Late Time Spend
1                       12:00             14:00           11:30                    14:30                      0                        90
1                       16:00             18:00           16:30                    18:30                    30                        90


Thank you very mush indeed for helping in advance and i truly need the solution and I can't find it out :(

Answer 10

declare @schedule table ( EMP nvarchar(10),  Date  datetime, TimeIn datetime, TimeOut datetime)
declare @emp table ( EMP nvarchar(10),  Date  datetime, TimeIn datetime, TimeOut datetime)

insert into @schedule 
select '1', '7-10-2009', '12:00', '14:00' union all
select '1', '7-10-2009', '16:00', '18:00'

insert into @emp 
 select '1' , '7-10-2009', '11:30', '12:30' union all
select '1' , '7-10-2009', '13:00', '14:30' union all
select '1' , '7-10-2009', '16:30', '18:30'

 
select 
convert(nvarchar, s.date, 105), 
convert(nvarchar, s.timein , 108) 'schedualed timein', 
convert(nvarchar, s.timeout , 108) 'schedualed timeout', 
convert(nvarchar, min(e.timein) , 108)   'First Check In',   
convert(nvarchar, max(e.timeout) , 108)    'Last Check Out', 
case when datediff(minute, s.timein, min(e.timein)  )  < 0 then 0 else  datediff(minute, s.timein, min(e.timein)  )  end 'Minutes Late', 
sum(datediff(minute, case when e.timein>s.timein then e.timein else s.timein end, case when e.timeout<s.timeout then e.timeout else s.timeout end)) as 'minutes spend' 
	from @schedule s 
	inner join @emp e on e.timein between s.timein and s.timeout
						or  e.timeout  between s.timein and s.timeout
 
 group by s.date, s.timein, s.timeout

Answer 11

Hello Arif
Thank you very for the solution it works

I would like to have your opinion About The query I wrote before, is that better to calculate the number of minutes on the fly?

this just an idea, I am trying to do it with optimal and best way

thank you indeed for your help
 

Hi Wael,

dont unmark the answer because it worked for your initial requirement it could be misleading for other people who will have the same requirement as your initial requirement.

You can mark multiple post as answer one for you initial requirement and one for your changed requirement

Regards

Arif

Answer 12

In fact
your query work fine but 
I have this case

EmpId               Time In          Time Out
1                       12:00             14:00
1                       16:00             18:00

And Activity

EmpId              Check In          Check Out
1                      11:30                 12:30
1                      13:00                 14:30


the query return (wish is right)

EmpId               Time In          Time Out     First Check In      Last Check Out      Minutes Late Time Spend
1                       12:00             14:00           11:30                    14:30                      0                        90

but it should also return


EmpId               Time In          Time Out     First Check In      Last Check Out      Minutes Late Time Spend
1                       16:00             18:00           null                             null                     0                       0

one more thing,
I would like to have you opinion about the performance of this query,
my idea is:

do you think, or is there is a way to fill the  First Check In      Last Check Out      Minutes Late Time Spend when the employee Checking out?
I could Add these fields to the Activity Table

thank you very mush.

Answer 13

declare @schedule table ( EMP nvarchar(10),  Date  datetime, TimeIn datetime, TimeOut datetime)
declare @emp table ( EMP nvarchar(10),  Date  datetime, TimeIn datetime, TimeOut datetime)

insert into @schedule 
select '1', '7-10-2009', '12:00', '14:00' union all
select '1', '7-10-2009', '16:00', '18:00'

insert into @emp 
 select '1' , '7-10-2009', '11:30', '12:30' union all
select '1' , '7-10-2009', '13:00', '14:30'
-- union all
--select '1' , '7-10-2009', '16:30', '18:30'

 
select 
convert(nvarchar, s.date, 105), 
convert(nvarchar, s.timein , 108) 'schedualed timein', 
convert(nvarchar, s.timeout , 108) 'schedualed timeout', 
convert(nvarchar, min(e.timein) , 108)   'First Check In',   
convert(nvarchar, max(e.timeout) , 108)    'Last Check Out', 
isnull(case when datediff(minute, s.timein, min(e.timein)  )  < 0 then 0 else  datediff(minute, s.timein, min(e.timein)  )  end, 0) 'Minutes Late', 
sum( case when e.timein is null then 0 else datediff(minute, case when e.timein>s.timein then e.timein else s.timein end, case when e.timeout<s.timeout then e.timeout else s.timeout end) end) as 'minutes spend' 
	from @schedule s 
	left outer join @emp e on e.timein between s.timein and s.timeout
						or  e.timeout  between s.timein and s.timeout
 
 group by s.date, s.timein, s.timeout

 

create indexes on timein and timeout on both table hopefuly it will work fine.

>>do you think, or is there is a way to fill the  First Check In      Last Check Out      Minutes Late Time Spend when the employee Checking out?
>>I could Add these fields to the Activity Table

I  think you mean to add in the schedual table (the first table). yes you can do that but the load will be transfered to the checkin checkout process and the employee feel slow response when he checks in/out

i think the best way is to bench mark both aproaches

please look into my previous post. and mark apropriat posts as answer.

Answer 14

why did you mark your own post as answer ?

Answer 15

sorry
I have marked the wrong post..
I fixed Now

Answer 16

Arif
it is Not working.......


Schedule Table:

EMPId        Date                 timein            timeout
     1       2009-11-04       13:08:00        00:08:00

Activity Table

EMP     Date            CheckIn    Check Out
1    2009-11-04    23:10:23    23:10:48
1    2009-11-04    23:16:34    23:20:32
1    2009-11-04    23:23:20    23:23:34
1    2009-11-04    23:23:35    23:27:07

The result is
date                Schedule Time in       Schedule Time Out      firstchekin    lastcheckout    minlate  spend
04-11-2009            13:08:00             00:08:00                         NULL        NULL                0          0

Please I need your help

Answer 17

declare @schedule table ( EMP nvarchar(10),  Date  datetime, TimeIn datetime, TimeOut datetime)
declare @emp table ( EMP nvarchar(10),  Date  datetime, TimeIn datetime, TimeOut datetime)

insert into @schedule 
select  '1', '2009-11-04', '13:08:00','00:08:00'

insert into @emp 
select '1',    '2009-11-04',   '23:10:23',    '23:10:48' union all
select '1',    '2009-11-04',   '23:16:34',    '23:20:32' union all
select '1',    '2009-11-04',   '23:23:20',    '23:23:34' union all
select '1',    '2009-11-04',   '23:23:35',    '23:27:07'



;with schedule as (select EMP, date, date+timein as timein, date+timeOut +(case when timein>=Timeout then 1 else 0 end ) as timeOut   from @schedule), 
 emp as (select EMP, date, date+timein as timein, date+timeOut +(case when timein>=Timeout then 1 else 0 end ) as timeOut   from @emp)


select 
convert(nvarchar, s.date, 105), 
convert(nvarchar, s.timein , 108) 'schedualed timein', 
convert(nvarchar, s.timeout , 108) 'schedualed timeout', 
convert(nvarchar, min(e.timein) , 108)   'First Check In',   
convert(nvarchar, max(e.timeout) , 108)    'Last Check Out', 
isnull(case when datediff(minute, s.timein, min(e.timein)  )  < 0 then 0 else  datediff(minute, s.timein, min(e.timein)  )  end, 0) 'Minutes Late', 
sum( case when e.timein is null then 0 else datediff(minute, case when e.timein>s.timein then e.timein else s.timein end, case when e.timeout<s.timeout then e.timeout else s.timeout end) end) as 'minutes spend' 
	from schedule s 
	left outer join emp e on e.timein between s.timein and s.timeout
						or  e.timeout  between s.timein and s.timeout
 
 group by s.date, s.timein, s.timeout

Hope your problem will be solved.
Please mark the posts as answers that soved your problem.