Thread: Checking for schedule conflicts
Given the tables defined below, what's the easiest way to check for schedule conflicts? So far, the only way I've come up with is to create a huge, multi-dimensional array in PHP, with a data element for every minute of all time taken up by all events, and then check for any of these minutes to be set as I go through all the records. (ugh!) But, how could I do this in the database? But I'd like to see something like "select count(*) FROM events, sched WHERE sched.date=$date AND events.id=sched.events_id ... GROUP BY date, start<finish and finish<start HAVING count(*) >1 " And here's where I get stumped. You can't group by start or end because we need to check if they OVERLAP any other records on the same date. Ideas? // Sometimes, recurring events create table events ( id serial not null primary key, title varchar ); // date=YYYYMMDD, start/end: HH:MM (24-hour) create table sched ( events_id integer not null references events(id), date integer not null, start integer not null, end integer not null ); insert into events (title) VALUES ('Tuesday Pickup'); insert into sched(events_id, date, start, end) VALUES (1, 20050308, 0900, 1300); insert into sched (events_id, date, start, end) VALUES (1, 20050315, 0900, 1300); -- "The best way to predict the future is to invent it." - XEROX PARC slogan, circa 1978
On Sat, 2005-03-12 at 00:13 -0800, Benjamin Smith wrote: > Given the tables defined below, what's the easiest way to check for schedule > conflicts? > > So far, the only way I've come up with is to create a huge, multi-dimensional > array in PHP, with a data element for every minute of all time taken up by > all events, and then check for any of these minutes to be set as I go through > all the records. (ugh!) > > But, how could I do this in the database? > > But I'd like to see something like > "select count(*) FROM events, sched > WHERE sched.date=$date > AND events.id=sched.events_id > ... > GROUP BY date, start<finish and finish<start > HAVING count(*) >1 " > > And here's where I get stumped. You can't group by start or end because we > need to check if they OVERLAP any other records on the same date. > > Ideas? use the OVERLAPS operator ? http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html gnari
Ragnar Hafstað wrote: > On Sat, 2005-03-12 at 00:13 -0800, Benjamin Smith wrote: > >>Given the tables defined below, what's the easiest way to check for schedule >>conflicts? >> >>So far, the only way I've come up with is to create a huge, multi-dimensional >>array in PHP, with a data element for every minute of all time taken up by >>all events, and then check for any of these minutes to be set as I go through >>all the records. (ugh!) >> >>But, how could I do this in the database? >> >>But I'd like to see something like >>"select count(*) FROM events, sched >> WHERE sched.date=$date >> AND events.id=sched.events_id >> ... >> GROUP BY date, start<finish and finish<start >> HAVING count(*) >1 " >> >>And here's where I get stumped. You can't group by start or end because we >>need to check if they OVERLAP any other records on the same date. >> >>Ideas? > > > use the OVERLAPS operator ? > http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html > > gnari The idea is to join table with itself so you can compare different records, something like: select * from sched a, sched b /* join with itself */ where (a.start between b.start and b.end /* filter out overlapping */ or a.end between b.start and b.end) and a.id != b.id /* event overlaps iself - leave that out */ or insted of 'between' use the OVERLAPS operator Ragnar mentioned when dealing with date types. Andre
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Benjamin Smith wrote: > Given the tables defined below, what's the easiest way to check for schedule > conflicts? Assuming you don't count more than once things with the same id, start, and finish: SELECT COUNT(DISTINCT (s1.event_id||s1.start||s1.finish)) FROM sched s1, sched s2 WHERE s1.date = s2.date AND s1.start >= s2.start AND s2.finish <= s2.finish AND NOT s1.ctid = s2.ctid Add "AND s1.date = $date" as needed. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200503140639 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFCNXgTvJuQZxSWSsgRAlBmAKC7hj8XzHzS7srqfgdSGOZiCfvtDQCfWM22 VXMkQB7IzEdTKjqpcmWVdaM= =hFiy -----END PGP SIGNATURE-----