Check Conflicting Rooms Query

need some logic help

[txbakers]

I need some help with the logic for this project. I'm a bit baffled and overwhelmed by the possibilities.

The main fields in the table are:
names
time
room

Sample data would include 1 name or several names, a time on the 24hr clock, and a room number.

I need to run a query to check that within a certain time frame, let's say 16 minutes, a person isn't duplicated in any of the rooms.

SO, if I had four rooms and the 8:15 time looked like this:

Room 1
John Smith

Room 2
Steve Wilson

Room 3
Betty Sanchez
Walter Winchell
Sally Singzwell
John Smith

Room 4
Edne Mole

the query or queries would throw a flag.

I need to check every time segment against every room I think, so that it doesn't have to be done manually.

Is it even feasible to write something like this? Is there a way I can write something like this and NOT have to run a query for every time segment? I'm a bit overwhelmed here.

[Dijkgraaf]

You want something like the following

select name,time, room
from room
where exists (select 1 from room room2 where room.time = room2.time and room.name = room2.name and room.room <> room2.room)
order by name, time, room

which returns
John Smith 2005-10-17 08:15:00.000 1
John Smith 2005-10-17 08:15:00.000 3

[txbakers]

thanks, i'll try that. My other concern is twofold.

I need to do this for a range of times, not just a direct match, so if one person is scheduled in room 1 for 8:15, they can't be scheduled anywhere else for 15 minutes before and 15 minutes after.

I might be able to use a between for this one.

Then, I also need to find a student in a group of names. The entry in Room 3 is one entry, with the names comma separated. I might try an "in" instead of an "=".

But will this query return a complete list of potential conflicts or will I have to run it for each room against every other room?

[Dijkgraaf]

Well you might be better of not having the names as comma seperated in a field, but rather have one record per person, otherwise down the track when the database gets bigger you will have performance problems.

e.g.
Name, time, room
John Smith,8:15:00 a.m.,1
Steve Wilson,8:15:00 a.m.,2
Betty Sanchez,8:15:00 a.m.,3
Walter Winchell,8:15:00 a.m.,3
Sally Singzwell,8:15:00 a.m.,3
John Smith,8:15:00 a.m.,3
Edne Mole,8:15:00 a.m.,4

Rather than a between you could do a abs(datediff(mi,room.time,room2.time)) <= 15
the syntax dependant on what version of SQL you are using. You will probably also have to make sure that negative differences are turned into positive ones hence the abs.

The query will return for all rooms and times unless you limit it via further conditions in the where clause.

Also to improve performance and reliability you might actually want to have a seperate names table, that gives each name an ID, and use that ID in the booking table to point to the name.

[edited by: Dijkgraaf at 2:18 am (utc) on Oct. 17, 2005]

[txbakers]

Maybe my data layout isn't clear. Here is a better data sample:

names time room
J Smith 8:15 1
B Jones 8:15 2
S Wilson,K Kravits, J Mason, S Dogg,j Smith 8:15 3
T Hatt 8:15 4
W Woople 8:20 1

I"m using mySQL 4.01

I'm getting an error on the EXISTS syntax, not sure why yet.

[txbakers]

oh - sorry, we cross posted!

I didn't use the ID/separate name entry because I'm really tracking events. Some events have more than one participant. Still, I might be better creating an event ID, then another table to match event ID to names.

But I'd rather not rewrite the program.

It's not that the person is scheduled for a time, rather the event is scheduled.

[Dijkgraaf]

Yes, I have problems getting the EXISTS to work in MySQL as well, not sure why as the syntax is a generic one that does seem to be supported by MySQL.
I was originally checking my syntax in MS-SQL, hence the comment about the format being dependant on what SQL you were using.

If the names are all in one field then you are making life a lot more difficult for yourself, as you would have to split the field on the commas and do a lot of other work to find a match.

[txbakers]

you are making life a lot more difficult for yourself

Oh yes!

I never had to worry about checking for conflicts though. Generally, the user makes their entry, whether one or multiple names didn't matter. It was just for display purposes because everything revolved around the event.

Now I'm trying to automate a schedule for the events which is easy enough to do, but I also need to provide some type of conflict validation.

IDs would be certainly easier, but I'm in ASP, so looping is not a problem in execution. If I need to do multiple queries I can do that. Sure, it's a hit on the server, but it doesn't happen constantly. Once or twice in a cycle is all it needs.

Too much shiraz tonight to think through it.

[txbakers]

I'm getting close - must be the wine.

select * from scheduletest st left join scheduletest st1 on st.names like concat('%',st1.names,'%') and st.stime = st1.stime and st.room <> st1.room where st1.names is not null

(using the real field and table names)

This does give me the exact matches on time comflicts. Now I need to do something with the range of time on either side. The datediff isn't used in my version, and I tried a variation of the date_add....

Hey - maybe I can use a between with two date_adds - one positive, one negative! Stay tuned

[Dijkgraaf]

However that would only work where you aren't comparing two events that both have lists of people.
In your example if in room 1 you had "John Smith, Walter Winchell" they would not get picked up as also being in room 3 at the same time.

[txbakers]

ugh, you are right.

And I still haven't convinced the date add to work. I even changed from a TIME type to a DATETIME type.

select * from scheduletest st left join scheduletest st1 on st.names like concat('%',st1.names,'%') and st.stime between date_add(st1.stime, interval 5 minute) and date_add(st1.stime, interval -5 minute) and st.room <> st1.room where st1.names is not null

brings back no records, while the exact match above brings back 3.

why doesn't that one work with the date/time range of 10 minutes?

[Dijkgraaf]

Try taking the date_add clauses out of the join and put them in the select, and see what values you are getting back, that will probably give you a clue.

[txbakers]

I did try that - got back the correct values. that's why I changed to a datetime type instead of a time. When it was time type all I got back were nulls.

I added the two date_add functions and did get back 5 min before and 5 min after.

[Dijkgraaf]

Try putting the 5 and -5 the other way around (-5 first then 5)

[txbakers]

D'OH! That worked stupendously.

I'm thinking that I can run this one for the single names and run something else for the groups.

But this query is a big step in that direction.

Thanks for your help with it!

[txbakers]

I figured out a pretty elegant solution for the multi-names issue.

I can read all the names and set up a temp table of individual names, then run my other query against the main table. Then just drop the table after I run the query.

Pretty sure that will work.

The next trick is to move the conflicted entries. I was thinking I would bring up an entry about 25 minutes away and just swap the times.

any type of auto-scheduler like this is going to be a mess and will require some multiple tweaking, but at least it makes it a little easier.