Room Timetable - Query

[denzyego]

Greetings!

Is it possible to share the sql script fetching the room timetable resulting to the data as shown in the attached screenshot?

I would like to create a sql view so as to visualize the room utilization and occupancy within a given period.

Thanks

[tomas-muller]

The page is not generated by a single query. The following HQL probably comes closest:

select
  m.event.eventName as Event_Name,
  date_format(m.meetingDate, '%Y-%m-%d') as Meeting_Date,
  case when m.startOffset is null then
    (str(floor((5 * m.startPeriod - (mod(5 * m.startPeriod, 60))) /60)) || ':' || lpad(str(mod(5 * m.startPeriod, 60)), 2, '0'))
  else
    (str(floor((5 * m.startPeriod + m.startOffset - (mod(5 * m.startPeriod + m.startOffset, 60))) /60)) || ':' || lpad(str(mod(5 * m.startPeriod + m.startOffset, 60)), 2, '0'))
  end as Start_Time,
  case when m.stopOffset is null then
    (str(floor((5 * m.stopPeriod - (mod(5 * m.stopPeriod, 60))) /60)) || ':' || lpad(str(mod(5 * m.stopPeriod, 60)), 2, '0'))
  else
    (str(floor((5 * m.stopPeriod + m.stopOffset - (mod(5 * m.stopPeriod + m.stopOffset, 60))) /60)) || ':' || lpad(str(mod(5 * m.stopPeriod + m.stopOffset, 60)), 2, '0'))
  end as Stop_Time,
  (select case when r.name is null then (r.buildingAbbv || ' ' || r.roomNumber) else r.name end from Location r where r.permanentId = m.locationPermanentId and r.session.eventBeginDate <= m.meetingDate and m.meetingDate <= r.session.eventEndDate) as Room
from Meeting m
where m.meetingDate >= str_to_date('2010-08-23', '%Y-%m-%d') and m.meetingDate <= str_to_date('2010-08-24', '%Y-%m-%d')

You can test this query using the Administration > Utilities > Test HQL page. It generates the following SQL:

select event1_.event_name as col_0_0_, date_format(meeting0_.meeting_date, '%Y-%m-%d') as col_1_0_, case when meeting0_.start_offset is null then concat(cast(floor((5*meeting0_.start_period-mod(5*meeting0_.start_period, 60))/60) as char), ':', lpad(cast(mod(5*meeting0_.start_period, 60) as char), 2, '0')) else concat(cast(floor((5*meeting0_.start_period+meeting0_.start_offset-mod(5*meeting0_.start_period+meeting0_.start_offset, 60))/60) as char), ':', lpad(cast(mod(5*meeting0_.start_period+meeting0_.start_offset, 60) as char), 2, '0')) end as col_2_0_, case when meeting0_.stop_offset is null then concat(cast(floor((5*meeting0_.stop_period-mod(5*meeting0_.stop_period, 60))/60) as char), ':', lpad(cast(mod(5*meeting0_.stop_period, 60) as char), 2, '0')) else concat(cast(floor((5*meeting0_.stop_period+meeting0_.stop_offset-mod(5*meeting0_.stop_period+meeting0_.stop_offset, 60))/60) as char), ':', lpad(cast(mod(5*meeting0_.stop_period+meeting0_.stop_offset, 60) as char), 2, '0')) end as col_3_0_, (select case when location2_.name is null then concat( (select b.abbreviation from timetable.building b where b.uniqueid = location2_.building_id) , ' ', location2_.room_number) else location2_.name end from ( select uniqueid, permanent_id, capacity, coordinate_x, coordinate_y, ignore_too_far, ignore_room_check, area, event_status, note, break_time, manager_ids, pattern, share_note, availability, exam_capacity, display_name, session_id, event_dept_id, external_uid, room_number, room_type, classification, building_id, null as name, 1 as clazz_ from timetable.room union select uniqueid, permanent_id, capacity, coordinate_x, coordinate_y, ignore_too_far, ignore_room_check, area, event_status, note, break_time, manager_ids, pattern, share_note, availability, exam_capacity, display_name, session_id, event_dept_id, external_uid, null as room_number, room_type, null as classification, null as building_id, name, 2 as clazz_ from timetable.non_university_location ) location2_, timetable.sessions session3_ where location2_.session_id=session3_.uniqueid and location2_.permanent_id=meeting0_.location_perm_id and session3_.event_begin_date<=meeting0_.meeting_date and meeting0_.meeting_date<=session3_.event_end_date) as col_4_0_ from timetable.meeting meeting0_, timetable.event event1_ where meeting0_.event_id=event1_.uniqueid and meeting0_.meeting_date>=str_to_date('2010-08-23', '%Y-%m-%d') and meeting0_.meeting_date<=str_to_date('2010-08-24', '%Y-%m-%d')

[denzyego]

Thank you sir for the prompt reply! This has come in handy

[denzyego]

One more request, is it possible to get the respective subject areas and course titles as part of this query ?

[tomas-muller]

See the following HQL:

select
  m.event.eventName as Event_Name,
  date_format(m.meetingDate, '%Y-%m-%d') as Meeting_Date,
  case when m.startOffset is null then
    (str(floor((5 * m.startPeriod - (mod(5 * m.startPeriod, 60))) /60)) || ':' || lpad(str(mod(5 * m.startPeriod, 60)), 2, '0'))
  else
    (str(floor((5 * m.startPeriod + m.startOffset - (mod(5 * m.startPeriod + m.startOffset, 60))) /60)) || ':' || lpad(str(mod(5 * m.startPeriod + m.startOffset, 60)), 2, '0'))
  end as Start_Time,
  case when m.stopOffset is null then
    (str(floor((5 * m.stopPeriod - (mod(5 * m.stopPeriod, 60))) /60)) || ':' || lpad(str(mod(5 * m.stopPeriod, 60)), 2, '0'))
  else
    (str(floor((5 * m.stopPeriod + m.stopOffset - (mod(5 * m.stopPeriod + m.stopOffset, 60))) /60)) || ':' || lpad(str(mod(5 * m.stopPeriod + m.stopOffset, 60)), 2, '0'))
  end as Stop_Time,
  (select case when r.name is null then (r.buildingAbbv || ' ' || r.roomNumber) else r.name end from Location r where r.permanentId = m.locationPermanentId and r.session.eventBeginDate <= m.meetingDate and m.meetingDate <= r.session.eventEndDate) as Room,
  co.subjectAreaAbbv as Subject_Area,
  co.courseNbr as Course_Nbr,
  co.title as Course_Title
from ClassEvent e inner join e.clazz.schedulingSubpart.instrOfferingConfig.instructionalOffering.courseOfferings co inner join e.meetings m
where m.meetingDate >= str_to_date('2010-08-23', '%Y-%m-%d') and m.meetingDate <= str_to_date('2010-08-24', '%Y-%m-%d')

Which generates the following SQL:

select event7_.event_name as col_0_0_, date_format(meetings6_.meeting_date, '%Y-%m-%d') as col_1_0_, case when meetings6_.start_offset is null then concat(cast(floor((5*meetings6_.start_period-mod(5*meetings6_.start_period, 60))/60) as char), ':', lpad(cast(mod(5*meetings6_.start_period, 60) as char), 2, '0')) else concat(cast(floor((5*meetings6_.start_period+meetings6_.start_offset-mod(5*meetings6_.start_period+meetings6_.start_offset, 60))/60) as char), ':', lpad(cast(mod(5*meetings6_.start_period+meetings6_.start_offset, 60) as char), 2, '0')) end as col_2_0_, case when meetings6_.stop_offset is null then concat(cast(floor((5*meetings6_.stop_period-mod(5*meetings6_.stop_period, 60))/60) as char), ':', lpad(cast(mod(5*meetings6_.stop_period, 60) as char), 2, '0')) else concat(cast(floor((5*meetings6_.stop_period+meetings6_.stop_offset-mod(5*meetings6_.stop_period+meetings6_.stop_offset, 60))/60) as char), ':', lpad(cast(mod(5*meetings6_.stop_period+meetings6_.stop_offset, 60) as char), 2, '0')) end as col_3_0_, (select case when location8_.name is null then concat( (select b.abbreviation from timetable.building b where b.uniqueid = location8_.building_id) , ' ', location8_.room_number) else location8_.name end from ( select uniqueid, permanent_id, capacity, coordinate_x, coordinate_y, ignore_too_far, ignore_room_check, area, event_status, note, break_time, manager_ids, pattern, share_note, availability, exam_capacity, display_name, session_id, event_dept_id, external_uid, room_number, room_type, classification, building_id, null as name, 1 as clazz_ from timetable.room union select uniqueid, permanent_id, capacity, coordinate_x, coordinate_y, ignore_too_far, ignore_room_check, area, event_status, note, break_time, manager_ids, pattern, share_note, availability, exam_capacity, display_name, session_id, event_dept_id, external_uid, null as room_number, room_type, null as classification, null as building_id, name, 2 as clazz_ from timetable.non_university_location ) location8_, timetable.sessions session9_ where location8_.session_id=session9_.uniqueid and location8_.permanent_id=meetings6_.location_perm_id and session9_.event_begin_date<=meetings6_.meeting_date and meetings6_.meeting_date<=session9_.event_end_date) as col_4_0_, ( select sa.subject_area_abbreviation from timetable.subject_area sa where sa.uniqueid = courseoffe5_.subject_area_id ) as col_5_0_, courseoffe5_.course_nbr as col_6_0_, courseoffe5_.title as col_7_0_ from timetable.event classevent0_ inner join timetable.class_ class_1_ on classevent0_.class_id=class_1_.uniqueid inner join timetable.scheduling_subpart scheduling2_ on class_1_.subpart_id=scheduling2_.uniqueid inner join timetable.instr_offering_config instroffer3_ on scheduling2_.config_id=instroffer3_.uniqueid inner join timetable.instructional_offering instructio4_ on instroffer3_.instr_offr_id=instructio4_.uniqueid inner join timetable.course_offering courseoffe5_ on instructio4_.uniqueid=courseoffe5_.instr_offr_id inner join timetable.meeting meetings6_ on classevent0_.uniqueid=meetings6_.event_id, timetable.event event7_ where classevent0_.event_type=0 and meetings6_.event_id=event7_.uniqueid and meetings6_.meeting_date>=str_to_date('2010-08-23', '%Y-%m-%d') and meetings6_.meeting_date<=str_to_date('2010-08-24', '%Y-%m-%d')

[denzyego]

Great! Thank you very much

[tomas-muller]

HQL including assigned instructors:

select
  m.event.eventName as Event_Name,
  date_format(m.meetingDate, '%Y-%m-%d') as Meeting_Date,
  case when m.startOffset is null then
    (str(floor((5 * m.startPeriod - (mod(5 * m.startPeriod, 60))) /60)) || ':' || lpad(str(mod(5 * m.startPeriod, 60)), 2, '0'))
  else
    (str(floor((5 * m.startPeriod + m.startOffset - (mod(5 * m.startPeriod + m.startOffset, 60))) /60)) || ':' || lpad(str(mod(5 * m.startPeriod + m.startOffset, 60)), 2, '0'))
  end as Start_Time,
  case when m.stopOffset is null then
    (str(floor((5 * m.stopPeriod - (mod(5 * m.stopPeriod, 60))) /60)) || ':' || lpad(str(mod(5 * m.stopPeriod, 60)), 2, '0'))
  else
    (str(floor((5 * m.stopPeriod + m.stopOffset - (mod(5 * m.stopPeriod + m.stopOffset, 60))) /60)) || ':' || lpad(str(mod(5 * m.stopPeriod + m.stopOffset, 60)), 2, '0'))
  end as Stop_Time,
  (select case when r.name is null then (r.buildingAbbv || ' ' || r.roomNumber) else r.name end from Location r where r.permanentId = m.locationPermanentId and r.session.eventBeginDate <= m.meetingDate and m.meetingDate <= r.session.eventEndDate) as Room,
  co.subjectAreaAbbv as Subject_Area,
  co.courseNbr as Course_Nbr,
  co.title as Course_Title,
  (case when i is null then '' else (i.lastName || ', ' || i.firstName) end) as Instructor
from ClassEvent e inner join e.clazz c inner join c.schedulingSubpart.instrOfferingConfig.instructionalOffering.courseOfferings co inner join e.meetings m
  left outer join c.classInstructors ci left outer join ci.instructor i
where m.meetingDate >= str_to_date('2010-08-23', '%Y-%m-%d') and m.meetingDate <= str_to_date('2010-08-24', '%Y-%m-%d')

Which generates the following SQL:

select event9_.event_name as col_0_0_, date_format(meetings6_.meeting_date, '%Y-%m-%d') as col_1_0_, case when meetings6_.start_offset is null then concat(cast(floor((5*meetings6_.start_period-mod(5*meetings6_.start_period, 60))/60) as char), ':', lpad(cast(mod(5*meetings6_.start_period, 60) as char), 2, '0')) else concat(cast(floor((5*meetings6_.start_period+meetings6_.start_offset-mod(5*meetings6_.start_period+meetings6_.start_offset, 60))/60) as char), ':', lpad(cast(mod(5*meetings6_.start_period+meetings6_.start_offset, 60) as char), 2, '0')) end as col_2_0_, case when meetings6_.stop_offset is null then concat(cast(floor((5*meetings6_.stop_period-mod(5*meetings6_.stop_period, 60))/60) as char), ':', lpad(cast(mod(5*meetings6_.stop_period, 60) as char), 2, '0')) else concat(cast(floor((5*meetings6_.stop_period+meetings6_.stop_offset-mod(5*meetings6_.stop_period+meetings6_.stop_offset, 60))/60) as char), ':', lpad(cast(mod(5*meetings6_.stop_period+meetings6_.stop_offset, 60) as char), 2, '0')) end as col_3_0_, (select case when location10_.name is null then concat( (select b.abbreviation from timetable.building b where b.uniqueid = location10_.building_id) , ' ', location10_.room_number) else location10_.name end from ( select uniqueid, permanent_id, capacity, coordinate_x, coordinate_y, ignore_too_far, ignore_room_check, area, event_status, note, break_time, manager_ids, pattern, share_note, availability, exam_capacity, display_name, session_id, event_dept_id, external_uid, room_number, room_type, classification, building_id, null as name, 1 as clazz_ from timetable.room union select uniqueid, permanent_id, capacity, coordinate_x, coordinate_y, ignore_too_far, ignore_room_check, area, event_status, note, break_time, manager_ids, pattern, share_note, availability, exam_capacity, display_name, session_id, event_dept_id, external_uid, null as room_number, room_type, null as classification, null as building_id, name, 2 as clazz_ from timetable.non_university_location ) location10_, timetable.sessions session11_ where location10_.session_id=session11_.uniqueid and location10_.permanent_id=meetings6_.location_perm_id and session11_.event_begin_date<=meetings6_.meeting_date and meetings6_.meeting_date<=session11_.event_end_date) as col_4_0_, ( select sa.subject_area_abbreviation from timetable.subject_area sa where sa.uniqueid = courseoffe5_.subject_area_id ) as col_5_0_, courseoffe5_.course_nbr as col_6_0_, courseoffe5_.title as col_7_0_, case when department8_.uniqueid is null then '' else concat(department8_.lname, ', ', department8_.fname) end as col_8_0_ from timetable.event classevent0_ inner join timetable.class_ class_1_ on classevent0_.class_id=class_1_.uniqueid inner join timetable.scheduling_subpart scheduling2_ on class_1_.subpart_id=scheduling2_.uniqueid inner join timetable.instr_offering_config instroffer3_ on scheduling2_.config_id=instroffer3_.uniqueid inner join timetable.instructional_offering instructio4_ on instroffer3_.instr_offr_id=instructio4_.uniqueid inner join timetable.course_offering courseoffe5_ on instructio4_.uniqueid=courseoffe5_.instr_offr_id left outer join timetable.class_instructor classinstr7_ on class_1_.uniqueid=classinstr7_.class_id left outer join timetable.departmental_instructor department8_ on classinstr7_.instructor_id=department8_.uniqueid inner join timetable.meeting meetings6_ on classevent0_.uniqueid=meetings6_.event_id, timetable.event event9_ where classevent0_.event_type=0 and meetings6_.event_id=event9_.uniqueid and meetings6_.meeting_date>=str_to_date('2010-08-23', '%Y-%m-%d') and meetings6_.meeting_date<=str_to_date('2010-08-24', '%Y-%m-%d')

[denzyego]

Thank You