schedule and only show avail. times

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)

schedule and only show avail. times

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2001-12-29 : 10:58:30

I have a list of appointments below. I am sending a
cldate(start) and a cldatend(end) date against this table.

ie. 2001-12-28 08:00:00, 2001-12-28 09:00:00

I want to return only the available resid(resources) as
a client selection.

I have also included part of the sproc to show you where
I am at. Any suggestions to keep the performance at a
maximum would be appreciated.

closeid resid cldate cldateend
------- ----------- ------------------------ ---------------------
1 1 2001-12-28 08:00:00 2001-12-28 09:00:00
2 2 2001-12-28 08:00:00 2001-12-28 10:00:00
3 5 2001-12-28 08:30:00 2001-12-28 12:00:00
4 12 2001-12-28 12:00:00 2001-12-28 13:00:00
5 1 2001-12-28 13:00:00 2001-12-28 14:00:00
6 2 2001-12-28 15:00:00 2001-12-28 16:00:00
7 1 2001-12-28 17:00:00 2001-12-28 18:00:00
8 12 2001-12-28 19:00:00 2001-12-28 20:00:00
9 5 2001-12-28 21:00:00 2001-12-28 22:00:00
10 12 2001-12-28 07:00:00 2001-12-28 10:00:00

ALTER PROCEDURE s_cal_clos_assing_pag

(
@oid int,
@closeid int, --this is the selected order the client wants to schedule
@ddate smalldatetime
)

as

SET NOCOUNT ON

--get all available resources for office

--set vars
declare @excludedatestart smalldatetime
declare @excludedateend smalldatetime

DECLARE @resources table (
recnum int identity (1,1),
resid int,
tableid int,
label varchar(30)
)
DECLARE @exclude table (
resid int
)

--set resource id(these are the dates I check table with)
set @resid=(select resid from tblorder_close where closeid=@closeid)
set @excludedatestart=(select cldate from tblorder_close where closeid=@closeid)
set @excludedateend=(select cldateend from tblorder_close where closeid=@closeid)

--grab all resources for this office(people)
insert @resources
select a.resid,
b.tableid,
b.label
from tblresource_join a
inner join tblresource b on a.resid=b.resid
where a.oid=@oid

--insert into exclude(THIS IS WHERE I NEED HELP)
--I need to get all my resources and then exclude the ones that
--have appointments between @excludedatestart and @excludedateend times
insert @exclude
select resid
from tblorder_close
where @excludedatestart not between cldate and cldateend or @excludedateend not between cldate and cldateend

--set return rs

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources