I have a table
rooms which contains information regarding events that take in rooms. This is the definition of the table:
create table room( sdate date,--start date stime time,--start time rno int,--room no bno int,--building no eid int,--event id duration interval, primary key(sdate, stime, rno, bno, eid) );
I want to find all events that are adjacent to one another, that is if event
x finishes at 10:00 and event
y starts at 10:00 (provided they're in the same room, building and date of course) these events are adjacent in terms of time.
I'm new to SQL and I wrote a query to find such events using 5 subqueries and one union which seems to me to be over the top. I'd like to learn the best practices therefore is there a better, more concise way to perform such a query?
This is the query:
with --calculate end times (etime) schedule_w_etime(sdate, stime, rno, bno, etime) as ( select sdate, stime, rno, bno, (stime + duration) as etime from schedule ), --find adjacent events result(sdate, stime, rno, bno, etime) as ( select * from schedule_w_etime as S where S.etime in (select stime from schedule_w_etime as T where S.sdate=T.sdate and S.rno=T.rno and S.bno=T.bno) ), --get the last event from result max_time(date, start_time, room_no, build_no, end_time) as ( select * from result where etime=(select max(etime) from result) ), --lookup the start time of the last event lookup_max_time(sdate, stime, rno, bno, etime) as ( select * from schedule_w_etime as S, max_time as M where S.stime=M.end_time and S.bno=M.build_no and S.rno=M.room_no ), --format lookup_max_time to only relevant columns last_event_final(sdate, stime, rno, bno) as ( select sdate, stime, rno, bno from lookup_max_time ) --union of the last event and all the other events from result (select * from last_event_final) union (select sdate, stime, rno, bno from result)
As you can see my main problem is with displaying the last event because in the
result view doesn't contain the start time of the last event.