Important Content Update Message
We are currently updating the OP Help Center content for the release of OP 20. OP 20 (official version 20.0.x) is the certified, 2015 Edition, version of the Office Practicum software. This is displayed in your software (Help tab > About) and in the Help Center tab labeled Version 20.0. We appreciate your patience as we continue to update all of our content.

SQL: Room Use

About

This report shows how frequently different rooms/resources were used between two schedule dates you specify.

A sample image of this SQL report run in the Database Viewer is shown below:

Caveats

  • Rooms and Resources must be set up in the Rooms table, and they must include a Colloquial name.
  • In order for visits to be included in the room count, they must also have a Visit Status.
  • You are prompted for dates at runtime, which will be interpreted as DATETIME.  If you put the same date for start and end, nothing will show.  If you only want to see one day, the start date should be that day and the end date should be the day following.

Code

To highlight and copy the code below to your clipboard, simply click the Copy button.

select count(su) as total_visits, rm_num from
(
select distinct schedule_pk_uniqkey as su, rm.colloquial_name as rm_num, 
loginname as schedule_provider


  from schedule_pat_trak 
inner join schedule on schedule.uniqkey = schedule_pat_trak.schedule_pk_uniqkey
inner join staff1 rm on rm.staffid = schedule_pat_trak.r_addr_id 
inner join staff1 pcp on pcp.staffid = schedule.addr_id
inner join appt_type on appt_type.appt_type_id = schedule_pat_trak.activity_status

where schedule_pat_trak.activity_starttime between :start_date and :end_date
order by schedule_pat_trak.activity_starttime desc
) group by rm_num
order by total_visits