not between - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

not between

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-02-14 : 08:52:32

I have a table of times start end. They are all on the same
date and the DT is smalldatetime. I have a input start and
and input end time that I need to check against to see the
available times. Using the between funcion works but takes
away the upper limit end time. This is what I have.

start end
1)8:00-10:00
2)9:00-10:00
3)8:00-9:00
4)10:00-12:00
5)9:00-12:00

@excludestart @excludeend
9:00-10:00

I have this:
where (@excludedatestart between start and end or @excludedateend between start and end)

this works but it will take away #4(10:00-12:00) because 10:00
is technically the upper limit and between 9:00-10:00. I don't want
to take away the start time of another because my ending time touches it.

How can I write it to only take away the times that my @exclude times
cross not touch.

ie:

where (@excludedatestart <=start and @exlude <= end....)

any help

slow down to move faster...

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-14 : 11:39:54

I'm not sure I understand exacly what you are doing, but try using < and > rather than between. (Not >= or <=) this will exclude situations where the times are exact matches.

-Chad

skillile
Posting Yak Master

208 Posts

Posted - 2002-02-14 : 11:47:58

Yep I got that.

My problem is that I have users assigned to my scheduled times (start
and end)

as the request for a new time comes in the @exclude times I cannot
show the users that have previous assingments in a drop down list.

the ><>=<=<> is where I think I need to be but, I can't figure out
the correct usage.

I want to first identify all the users ie (start end times) that
have previous engagements.

Hope that helps

slow down to move faster...

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-02-14 : 17:41:33

S,

I didn't quite understand what you were trying to do. Anyways, this code produces the following result:

tmstart tmend
-------------- --------------
2002/2/1 10:00 2002/2/1 12:00
2002/2/1 8:00 2002/2/1 9:00

---------------------------------------------------------------
select
tmstart='2002/2/1 8:00', tmend='2002/2/1 10:00'
into #temp
union select tmstart='2002/2/1 9:00', tmend='2002/2/1 10:00'
union select tmstart='2002/2/1 8:00', tmend='2002/2/1 9:00'
union select tmstart='2002/2/1 10:00', tmend='2002/2/1 12:00'
union select tmstart='2002/2/1 9:00', tmend='2002/2/1 12:00'

declare @excludestart datetime, @excludeend datetime
select @excludeStart='2002/2/1 9:00',@excludeEnd='2002/2/1 10:00'

select *
from #temp
where
-- rows that will end before the exclude start
datediff(hh,tmEnd,@excludeStart)>=0
or
-- rows that will start after the exclude end
datediff(hh,tmStart,@excludeEnd)<=0

skillile
Posting Yak Master

208 Posts

Posted - 2002-02-15 : 08:49:28

/*
OK this is where I am at.
The resid is a person.
I am trying to figure out the people that can NOT
accept the incoming time (@exclude) times.

so:
select * from #temp where (give me the people that are already
scheduled during the @exclude time range)

I can't use the between becuause it excludes the starting time.
ANY Suggestions would be appreciated.
--****************************************
*/

/*create table #temp (resid int, stime smalldatetime, etime smalldatetime)

insert #temp values (1, '2-15-2002 08:00:00', '2-15-2002 09:00:00')
insert #temp values (1, '2-15-2002 10:00:00', '2-15-2002 11:00:00')
insert #temp values (2, '2-15-2002 08:00:00', '2-15-2002 09:00:00')
insert #temp values (2, '2-15-2002 11:00:00', '2-15-2002 12:00:00')
insert #temp values (3, '2-15-2002 09:00:00', '2-15-2002 10:00:00')
insert #temp values (3, '2-15-2002 11:00:00', '2-15-2002 12:00:00')
*/

select * from #temp

declare @excludestart smalldatetime
declare @excludeend smalldatetime

set @excludestart='2-15-2002 09:00:00'
set @excludeend='2-15-2002 10:00:00'

select * from #temp
where
(@excludestart > stime and etime <= @excludeend ) or
(@excludestart > stime and etime >= @excludeend )

--select * from #temp
--where ((@excludestart < stime) or (@excludeend > etime ))

/*
select * from #temp
where
(stime < @excludestart and etime <= @excludeend) or
(stime < @excludestart and etime >= @excludeend) or
(stime > @excludestart and etime <= @excludeend)
*/

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources