Ensuring join between 2 tables on multiple values

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

Transact-SQL (2000)

Ensuring join between 2 tables on multiple values

Author

Topic

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-06-09 : 15:13:53

I have an appointment scheduling system (I've sort of inherited) that has columns for a typical scheduler where there are time slots and the resource that the time slots are for. And another table that contains the resources id's.

Resources could be things like a room, or a chair.

For example the appointment_slot table might have:
11:00 Room1
11:00 Room2

Meaning that at 11:00 I am able to schedule something for Room1 and I'm also able to schedule something in Room2. Pretty straightforward to this point. So I can issue a pretty simple query against the appointment slot table and say show me the first 10 times where Room1 is available, and voila I get a result set and 11:00 is on it.

The complication comes into play when events happened to require multiple rooms. Lets say I want to hold something that requires both Room1 and Room2. I'm having problems figuring out how to query in such a way as to say "Show me the times where Room1 and Room 2 are available." But the kicker is that the # of resources that are queried for is flexible I can't just put a simple AND in the where clause. The internals of the stored procedure I'm dealing with creates a simple TABLE variable that contains the resource Id's that are required. That table could contain 1, 2, 10, 15 resources potentially.

Right now the stored procedure just does the simple type select and says if any of the resources are available then add them to a temporary table. Afterwards it goes through a query that takes a long time that groups all of the time slots by location, day, time, having count(resource_id) = # of resources total. That query takes a very long percentage of the time for the overall stored procedure, and the procedure takes a very long time as well because it requires a complete table scan to do the count(resource_id) and the group by since it can't eliminate anything. The query in general takes a long time to run because it has to insert tons of records into the temp table because 1 of the resources is available.

Any ideas at all are welcomed. Basic table structures are pretty simple:

Appointment_Slots:
Appt_Date char(8) -- 20061231 for example
Appt_Time char(4) -- 1400 for example for 2 PM
Resource_id int -- fk to resource table
Appt_Free char(1) -- Y the appt is free, N the appt is being used

Resources:
Resource_id int -- PK just any old number
Description char(25) -- Room1 for example

#ResourcesNeeded -- Temporary table inside of the stored proc
Resource_id int -- contains the resource id's that are needed

Note - The actual appt table contains other fields dealing with the length of time it is free but I won't complicate the issue with that.

Thanks in advance,
Dalton

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-09 : 15:50:03

[code]-- prepare test data
declare @rooms table (roomid int, roomname varchar(16))

insert @rooms
select 1, 'Room A' union all
select 2, 'Room B' union all
select 3, 'Room C'

declare @slots table (roomid int, slottime smalldatetime)

insert @slots
select 1, '10:00' union all
select 1, '11:00' union all
select 1, '12:00' union all
select 1, '13:00' union all
select 2, '10:00' union all
select 2, '12:00' union all
select 2, '14:00' union all
select 2, '16:00' union all
select 3, '12:00' union all
select 3, '09:00' union all
select 3, '15:00' union all
select 3, '13:00'

-- initialize parameters for wished rooms
declare @wantedrooms int, @wantedtime smalldatetime

select @wantedrooms = 3,
@wantedtime = '12:00'

-- getting the rooms and time were all needed rooms are free
SELECT s.roomid,
r.roomname
FROM @slots s
INNER JOIN (
select slottime
from @slots
where slottime = @wantedtime
group by slottime
having count(*) >= @wantedrooms
) t on t.slottime = s.slottime
INNER JOIN @rooms r on r.roomid = s.roomid[/code]

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-06-09 : 16:57:15

Peso, Thank you for the suggestion. I'm seeing the kind of results I need, now I have to correlate this to the real stored procedure and real tables, but I think the performance of this is going to go a long way.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources