October 6, 2009 at 11:54 pm
Hello all 🙂
I have a table that contains the employees time in time out
Employee Id
Date
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-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-1 7-10-2009 8:44 12:00
EMP-1 7-10-2009 12:30 15:30
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.
Is that possible to do it , to calculate the number of minutes of an employee based on his exact schedule?
Can any one help me plz?:blush:
Any hint any idea is the most welcome.
October 7, 2009 at 1:38 am
Something like this ?
If not suitable please post DDL and sample data in the form of inserts
with cteTiming(EmployeeId,Date,Start,End)
as
(
select EmployeeId,
Date,
case when TimeIn < StartTime then StartTime else timeIn end,
case when TimeOut > EndTime then EndTime else timeOut end,
from #sched join #activity
on #sched.EmployeeId = #activity.EmployeeId
and #sched.date = #activity.date
)
Select EmployeeId,Date,sum(datediff(mm,Start,End))
from cteTiming
group by EmployeeId,Date
October 7, 2009 at 2:10 am
How does this system handle night shifts ?
EMP-1 7-10-2009 22:00 06:00 (next day) ??
If that is the case, you should add a day when calculating the end datetime.
Is this SQL2008 (date / time column datatype) ?
I hope you are using the correct datatype with you date and time columns !!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 7, 2009 at 4:52 am
in fact yes I am using sql server 2008
for the date I am using Date type
for the time I am using Datetime value
in fact i have an idea to add a new field to the table (timein_out) and each time when I update the table to fill the time out I will calculate the number of minutes.
October 7, 2009 at 6:20 am
What about sequenced shifts .. should the emp clock out and back in ?
I have elaborated a bit on Dave Ballantynes provided script.
declare @sched table ( EmployeeId int not null,
[Date] date not null default GETDATE(),
StartTime datetime not null default current_timestamp,
EndTime datetime null );
Insert into @sched values ( 1,'2009-10-01', '2009-10-01 09:00', '2009-10-01 12:00' );
Insert into @sched values ( 1,'2009-10-01', '2009-10-01 18:00', '2009-10-01 22:00' );
Insert into @sched values ( 1,'2009-10-02', '2009-10-02 09:00', '2009-10-02 17:00' );
Insert into @sched values ( 1,'2009-10-03', '2009-10-03 14:00', '2009-10-03 22:00' );
Insert into @sched values ( 1,'2009-10-04', '2009-10-04 14:00', '2009-10-04 22:00' );
Insert into @sched values ( 2,'2009-10-01', '2009-10-01 09:00', '2009-10-01 17:00' );
Insert into @sched values ( 2,'2009-10-02', '2009-10-02 09:00', '2009-10-02 17:00' );
Insert into @sched values ( 2,'2009-10-03', '2009-10-03 14:00', '2009-10-03 22:00' );
Insert into @sched values ( 2,'2009-10-04', '2009-10-04 14:00', '2009-10-04 22:00' );
Declare @activity table ( EmployeeId int not null,
[Date] date not null default GETDATE(),
TimeIn datetime not null default current_timestamp,
TimeOut datetime null );
Insert into @activity values ( 1,'2009-10-01', '2009-10-01 08:50', '2009-10-01 12:05' );
Insert into @activity values ( 1,'2009-10-01', '2009-10-01 19:00', '2009-10-01 22:30' ); -- late
Insert into @activity values ( 1,'2009-10-02', '2009-10-02 08:55', '2009-10-02 17:25' );
Insert into @activity values ( 1,'2009-10-03', '2009-10-03 14:15', '2009-10-03 22:15' ); -- late
Insert into @activity values ( 1,'2009-10-04', '2009-10-04 13:45', '2009-10-04 22:05' );
Insert into @activity values ( 2,'2009-10-01', '2009-10-01 09:00', '2009-10-01 17:00' );
Insert into @activity values ( 2,'2009-10-02', '2009-10-02 09:00', '2009-10-02 17:00' );
Insert into @activity values ( 2,'2009-10-03', '2009-10-03 14:00', '2009-10-03 22:00' );
Insert into @activity values ( 2,'2009-10-04', '2009-10-04 13:55', '2009-10-04 22:10' );
with cteTiming(EmployeeId,Date,CalcTimeIn,CalcTimeOut)
as
(
select S.EmployeeId,
S.Date,
case when A.TimeIn < S.StartTime then S.StartTime else A.timeIn end as CalcTimeIn ,
case when A.TimeOut > S.EndTime then S.EndTime else A.timeOut end as CalcTimeOut
from @sched S
left join @activity A
on S.EmployeeId = A.EmployeeId
and S.date = A.date
and S.StartTime between DATEADD(hh,-1,A.timeIn) and DATEADD(hh,+1,A.timeIn) -- select a single schedule !
)
/*
Select EmployeeId,Date,CalcTimeIn,CalcTimeOut
,datediff(Mi,CalcTimeIn,CalcTimeOut) as CalcMinutes
from cteTiming
order by EmployeeId, Date
*/
Select EmployeeId,Date,sum(datediff(Mi,CalcTimeIn,CalcTimeOut)) as CalcMinutes
from cteTiming
group by EmployeeId,Date
order by EmployeeId, Date
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 7, 2009 at 10:20 am
First of all, Thank you very much indeed for your help
about the night shift question:
logically the employee mush check out and then in the night shift he must check in and then check out at the end of his schedule.
But in real life, sometimes, the employee will not check out then check in again; he will only check in in the morning then check out in the evening.
Beside I was thinking to add new field to the table activity:
Row_Numb ==> auto number
Employee_Id
Date
Time_In
Time_Out
Number_of_minutes
and the query became
set @Row_Numb = (Select max(Row_Numb)
from Tbl_activity
where Employee_Id = @Employee_Id
and date = @date
if @Row_Numb is null -- mean this is a check in
begin
insert into Tbl_activity
values( @Employee_Id
, @date
, getdate()
, Null
, Null )
end
else
begin
set @chek_In = (select Time_In
from Tbl_activity
where Row_Numb=@Row_Numb)
set @chek_out= (select Time_Out
from Tbl_activity
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 Tbl_activity
values(@Employee_Id
,@date
, getdate()
, Null
,Null)
end
end
thank you very much indeed for you help.
October 8, 2009 at 1:49 pm
so!
no one want to help me a bit on this part ?
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
realy any help is the most welcom .
October 9, 2009 at 6:18 am
General comment, no way that when I'm working with you I'd be clocking in even one minute early or out one minute late, if I'm not going to have it counted.
More likely I wouldn't be working there.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 9, 2009 at 11:50 pm
I loved your comment 🙂
But still, the problem exist and their is no solution for this question till now..
hope to find an answer in this forums..
October 12, 2009 at 12:26 am
Some nasty little virus kept me in bed the last days of last week. :sick:
If you read my script, you'll see a section of the where clause where I added a comment on why I added that section to determine the correct shift (based on the start time).
You could also add a new inner join where you select the max shift start time that is previous to the actual starttime + e.g. one hour.
Keep in mind, only selecting by employee_id will not do the trick.
You need the correct date and starttime (boundary principle) to select the correct shift.
Maybe the article on tally tables can help you with the composition of your Shifts table.
http://www.sqlservercentral.com/articles/T-SQL/62867/
( search for subtopic "Dozens of Other Uses" ... How about making a "shift" table with 3 shifts per day starting on '2008-01-01 06:00' for the next 5 years...
another edit :Whistling:
You can avoid some calculation issues to actually store the datetime without seconds information !
e.g. insert into Tbl_activity
values( @Employee_Id
, @date
, dateadd(mi, datediff(mi, 0, GETDATE()), 0) -- always store 00.000 for seconds information
, Null
, Null )
Best is to apply the same when updating the endtime info.
declare @Starttime datetime
declare @Endtime datetime
Select @Starttime = DATEADD(SS, -30, GETDATE())
, @Endtime = GETDATE()
select @Starttime as Starttime, @Endtime as Endtime, datediff(mi, @Starttime , @Endtime) as diff_mi
Starttime Endtime diff_mi
2009-10-12 09:18:05.0172009-10-12 09:18:35.0170
2009-10-12 09:19:32.9272009-10-12 09:20:02.9271
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 12, 2009 at 12:33 pm
Welcome back ALZDBA.
well In fact I found something
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-2', '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' , '20:30' , '21:00' union all
select 'EMP-2' , '7-10-2009' , '10:30' , '12:00' union all
select 'EMP-2' , '7-10-2009' , '12:30' , '15:00' union all
select 'EMP-1' , '7-10-2009' , '21:30' , '22:15'
select
s.date,
s.timein 'schedualed timein',
s.timeout 'schedualed timeout',
s.EMP,
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 ,@emp e
where (e.timein between s.timein and s.timeout
or e.timeout between s.timein and s.timeout)
and s.EMP = e.EMP
--and s.EMP = 'EMP-2'
group by s.date, s.timein, s.timeout,s.EMP
this way I am avoiding CTE beside I am still trying to do it on the fly, this way i will gain a lot performance when i will load a reports, it is a simple query
so what do you think? should i keep this way or do the calculation on the fly when the employee check Out?(I will add a cursor to be on the right shift)
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply