SQL Server Performance Forum – Threads Archive

Join between 2 tables with multiple values needed

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(<img src=’/community/emoticons/emotion-11.gif’ alt=’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

Any reason you can’t using an "in" clause to query using a list of needed resources, or join to a table containing a list of the needed resources?

Thank you for the suggestion. It should help clarify. The IN would let me find an appointment time where Room1 or Room2 are available. I need to find a time where both Room1 and Room2 are available. Thanks,
Dalton

Ah, I see — select times where room1 is free and room 2 is free … and roomN is free. Interesting puzzle. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> I can think of a dynamic SQL solution that’d be workable, but I can’t come up with a standard query off the top of my head. Friday-itis maybe.

A question : are "appointments" guarnteed unique, and do they always have the same times (11:00, from your example)?

I’m thinking along these lines (I use datetime, not character fields for date and time, but the technique would be the same) You could choose ALL the available appointments for all the needed resources, group on the timeslot, then retain only those timeslots where the number of available resources is the same as the number you need. If all resources are scheduled with exactly the same appointments, it should work. Something like: select apptTime, count(*) as numResources
from appointment_slots a
inner join neededResources r on a.resource_id = r.resource_id
where appt_free = ‘Y’
group by apptTime
having count(*) = ( select count(*) from neededResources )

quote:Show me the times where Room1 and Room 2 are available.

Here is the best solution from performance point of view I figured out:
Select a1.Appt_Date, a1.Appt_Time
from Appoitment a1
join Appoitment a2 on a1.Appt_Date = a2.Appt_Date and a1.Appt_Time = a1.Appt_Time
where a1.resource_id = @room1_id
and a2.resource_id = @room2_id
and a1.appt_free = ‘Y’
and a2.appt_free = ‘Y’ However, to apply that approach as a general solution you would have to use dynamic sql.
Two main drowbacks of dynamic sql in this case are:
1. You would have to give table read rights to the account running the query, it is not enough to have rights to run stored procedure.
2. For really big number of resources it might be impractical if not impossible to use that approach. The solution with group by and count(*) doesn’t perform as well but it is easier to implement and it doesn’t require dynamic sql. Another possible solution is to insert free times from the first resource into temp table and then to delete in cycle (or mark occupied) times that are not free for subsequent resources.

]]>