2

I have a set of Meeting rooms and meetings in that having start date and end Date. A set of meeting rooms belong to a building.

The meeting details are kept in MeetingDetail table having a startDate and endDate. Now I want to fire a report between two time period say reportStartDate and reportEndDate, which finds me the time slots in which all the meeting rooms are booked for a given building

Table structure MEETING_ROOM - ID, ROOMNAME, BUILDING_NO

MEETING_DETAIL - ID, MEETING_ROOM_ID, START_DATE, END_DATE

The query has to be fired for reportStartDate and REportEndDate

Just to clarify further, the aim is to find all the time slots in which all the meeting rooms were booked in a given time period of reportStartDate and reportEndDate

Improve this question
2
  • which database? SQL Server, Oracle, MySQL... Commented Aug 3, 2010 at 6:20
  • Added mysql tag - see lalit's comment on potatopeeling's answer. Commented Aug 3, 2010 at 11:13

2 Answers 2

Reset to default
4

For SQL Server 2005+ you could try the following (see note at the end for mysql)

WITH TIME_POINTS (POINT_P) AS (SELECT DISTINCT START_DATE FROM MEETING_DETAIL WHERE START_DATE > @reportStartDate AND START_DATE < @reportEndDate UNION SELECT DISTINCT END_DATE FROM MEETING_DETAIL WHERE END_DATE > @reportStartDate AND END_DATE < @reportEndDate UNION SELECT @reportEndDate UNION SELECT @reportStartDate), WITH TIME_SLICE (START_T, END_T) AS (SELECT A.POINT_P, MIN(B.POINT_P) FROM TIMEPOINTS A INNER JOIN TIMEPOINTS B ON A.POINT_P > B.POINT_P GROUP BY A.POINT_P), WITH SLICE_MEETINGS (START_T, END_T, MEETING_ROOM_ID, BUILDING_NO) AS (SELECT START_T, END_T, MEETING_ROOM_ID, BUILDING_NO FROM TIME_SLICE A INNER JOIN MEETING_DETAIL B ON B.START_DATE <= A.START_T AND B.END_DATE >= B.END_T INNER JOIN MEETING_ROOM C ON B.MEETING_ROOM_ID = C.ID), WITH SLICE_COUNT (START_T, END_T, BUILDING_NO, ROOMS_C) AS (SELECT START_T, END_T, BUILDING_NO, COUNT(MEETING_ROOM_ID) FROM SLICE_MEETINGS GROUP BY START_T, END_T, BUILDING_NO), WITH ROOMS_BUILDING (BUILDING_NO, ROOMS_C) AS (SELECT BUILDING_NO, COUNT(ID) FROM MEETING_ROOM GROUP BY BUILDING_NO) SELECT B.BUILDING_NO, A.START_T, A.END_T FROM SLICE_COUNT A. INNER JOIN ROOMS_BUILDING B WHERE A.BUILDING_NO = B.BUILDING_NO AND B.ROOMS_C = A.ROOMS_C;

what it does is (each step corresponds to each CTE definition above)

  • Get all the time markers, i.e. end or start times
  • Get all time slices i.e. the smallest unit of time between which there is no other time marker (i.e. no meetings start in a time slice, it's either at the beginning or at the end of a time slice)
  • Get meetings for each time slice, so now you get something like
 10.30 11.00 Room1 BuildingA 10.30 11.00 Room2 BuildingA 11.00 12.00 Room1 BuildingA
  • Get counts of rooms booked per building per time slice
  • Filter out timeslice-building combinations that match the number of rooms in each building

Edit

Since mysql doesn't support the WITH clause you'll have to construct views for each (of the 5) WITH clases above. everything else would remain the same.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

+1 Nice way to generate the time slices! I'd generate a separate table for that, but this works much better.
oh, then you'll have to make all those CTE clauses as mysql views. performance will be bad though, because of all the chained views.
2

After reading your comment, I think I understand the problem a bit better. As a first step I would generate a matrix of meeting rooms and time slots using cross join:

select * from ( select distinct start_date , end_date from @meeting_detail ) ts cross join @meeting_room mr

Then, for each cell in the matrix, add meetings in that timeslot:

left join @meeting_detail md on mr.id = md.meeting_room_id and ts.start_date < md.end_date and md.start_date < ts.end_date

And then demand that there are no free rooms. For example, by saying that the left join must succeed for all rooms and time slots. A left join succeeds if any field is not null:

group by mr.building_no , ts.start_date , ts.end_date having max(case when md.meeting_room_id is null then 1 else 0 end) = 0

Here's a complete working example. It's written for SQL Server, and the table variables (@meeting_detail) won't work in MySQL. But the report generating query should work in most databases:

set nocount on declare @meeting_room table (id int, roomname varchar(50), building_no int) declare @meeting_detail table (meeting_room_id int, start_date datetime, end_date datetime) insert @meeting_room (id, roomname, building_no) select 1, 'Kitchen', 6 union all select 2, 'Ballroom', 6 union all select 3, 'Conservatory', 7 union all select 4, 'Dining Room', 7 insert @meeting_detail (meeting_room_id, start_date, end_date) select 1, '2010-08-01 9:00', '2010-08-01 10:00' union all select 1, '2010-08-01 10:00', '2010-08-01 11:00' union all select 2, '2010-08-01 10:00', '2010-08-01 11:00' union all select 3, '2010-08-01 10:00', '2010-08-01 11:00' select mr.building_no , ts.start_date , ts.end_date from ( select distinct start_date , end_date from @meeting_detail ) ts cross join @meeting_room mr left join @meeting_detail md on mr.id = md.meeting_room_id and ts.start_date < md.end_date and md.start_date < ts.end_date group by mr.building_no , ts.start_date , ts.end_date having max(case when md.meeting_room_id is null then 1 else 0 end) = 0

This prints:

building_no start end 6 2010-08-01 10:00:00.000 2010-08-01 11:00:00.000
Improve this answer

4 Comments

This will give me the meeting rooms that are booked. What I am looking for is all the time slots in which all the meeting rooms in a building were booked
@lalit: How do you define time slot?
the time slot is basically a start time and end time in which all the meeting rooms in the building are booked. For example Meeting room 1 m1 and Meeting room 2 m2 are in a building. and let's say m1 is booked from 10 am to 4 pm and m2 is booked for 2pm to 6 pm in a given day. So the overlapped time slot in which both the rooms are booed is 2pm to 4pm. This is overlapped time slot. Similarly we have ti find all the overlapped time slot for the reporting period. Also each building has different number of meeting rooms.
Thanks. This has given me good direction. Once I complete it I will post my final query here.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.