CODEWITHSUNDEEP
sqlmysql
lalit
lalit

Reputation: 1475

Finding overlapping dates

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

Upvotes: 2

Views: 894

Answers (2)

potatopeelings
potatopeelings

Reputation: 41075

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.

Upvotes: 4

Andomar
Andomar

Reputation: 238068

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

Upvotes: 2

Related Questions