Welcome to WebmasterWorld Guest from 220.127.116.11
Forum Moderators: open
The main fields in the table are:
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:
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.
select name,time, 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
John Smith 2005-10-17 08:15:00.000 1
John Smith 2005-10-17 08:15:00.000 3
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?
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]
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.
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.
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.
you are making life a lot more difficult for yourself
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.
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
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?
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.