Help with date range - 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)

Help with date range

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-03-18 : 23:05:58

I have a table of orders (tblorder) that I need to assign a resource to each order. The resources are in a seperate table (tblresource). The orders are on a time basis with a start and end time. As the order is selected I can only show the available resources that are
not being used at that time. Here goes:

tblresource
-----------

resid name
1 joe
2 mary
3 john

tblorder (all dates are smalldatetime)
-------

ordid resid starttime endtime
# 1 8:00 10:00
# 1 10:00 11:00
# 2 9:00 10:00

Now a new order is added that looks like this

ordid resid starttime endtime
# 10:00 11:00

I need to populate a dropdown list of only the
available resources. Something like this.

select closeid,
resid,
resoid
from tblorders
where (@excludedatestart between dateadd(mi,-1,cldate) and dateadd(mi,-1,cldateend)
OR dateadd(mi,-1,@excludedateend) between cldate and cldateend
OR cldate between @excludedatestart AND dateadd(mi,-1,@excludedateend)
OR cldateend between @excludedatestart AND @excludedateend)

This doesn't work HELP PLEASE.

Thanks in advance..

slow down to move faster...

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-03-19 : 01:47:35

skillie,

It is a pretty confusing question with out desired output or a lrger set but see if you can work from this


declare @Start smalldatetime 
declare @End smalldatetime 
set @Start = '10:00'
set @End = '11:00'

Select R.ResID from tblResource R
where not exists
(Select 1 from tblOrder where ResID = R.ResID and (dateadd(mi,1,StartTime) between @Start and @End
or dateadd(mi,-1,EndTime) between @Start and @End))

HTH

DavidM

Tomorrow is the same day as Today was the day before.

skillile
Posting Yak Master

208 Posts

Posted - 2002-03-19 : 05:46:26

Yeah, Sorry for the confusion. I will set this up and try it. I basically want to exclude any resid that are being used at that time and show the all others.

I can post a larger dataset or my tbl def if that helps?

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources