July 26, 2006 at 9:06 am
I am trying to figure out the best way to create a snap shot of hours worked during a certain time period by employees. I have something working but it's really ugly and was wondering if someone could come up with a really slick solutions!!
I have 4 jobs:
# 1.) Started at 8am to 4pm
# 2.) Started at 10am to 8pm
# 3.) Started at 1pm to 4pm
# 4.) Started at 9 am to 4pm
BUT I need to know how many hours were worked between 11am to 5pm.
Therefore
Job 1 would be 5 hours (11am to 4pm)
Job 2 would be 6 hours (11am to 5pm)
Job 3 would be 3 hours (1pm to 4pm)
Job 4 would be 5 hours (11am to 4pm)
Key fields in the table are;
nPK INT
nJobID INT
tStartTime DATETIME
tEndTime DATETIME
lJobOpen BOOLEAN
Regards
So job # 1 is
July 26, 2006 at 9:21 am
select case
when tstarttime=@endtime then datediff(hh,@starttime,@endtime)
when tstarttime>=@starttime and tendtime>=@endtime then datediff(hh,tstarttime,@endtime)
when tstarttime<=@starttime and tendtime=@starttime and tendtime<=@endtime then datediff(hh,tstarttime,tendtime)
else '0' end
from mytable
or thereabouts
MVDBA
July 26, 2006 at 10:00 am
or something like:
declare @t table
(
nPK int not null
,nJobID int not null
,tStartTime datetime not null
,tEndTime datetime null
,lJobOpen bit not null -- is the needed if tEndTime is null?
)
insert @t
select 1, 1, '20060726 11:00', '20060726 16:00', 1 union all
select 2, 1, '20060726 10:00', '20060726 20:00', 1 union all
select 3, 2, '20060726 13:00', '20060726 16:00', 1 union all
select 4, 2, '20060726 09:00', '20060726 16:00', 1
declare @reportStart datetime, @reportEnd datetime
select @reportStart = '20060726 11:00', @reportEnd = '20060726 17:00'
select ceiling(sum(datediff(minute, tStartTime, tEndTime))/60.0)
from ( select case when tStartTime < @reportStart
then @reportStart
else tStartTime
end as tStartTime
,case when tEndTime > @reportEnd
then @reportEnd
else tEndTime
end as tEndTime
from @t
where tEndTime > @reportStart
and tStartTime < @reportEnd ) D
July 26, 2006 at 10:10 am
That's it exactly! Beauty thanks!
July 26, 2006 at 10:50 am
Opps...
select ceiling(sum(datediff(minute, tStartTime, tEndTime))/60.0)
from ( select case when tStartTime < @reportStart
then @reportStart
else tStartTime
end as tStartTime
,case when (tEndTime > @reportEnd OR tEndTime IS NULL) --!!!!!
then @reportEnd
else tEndTime
end as tEndTime
from @t
where tEndTime > @reportStart
and tStartTime < @reportEnd ) D
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply