We are currently updating the OP Help Center content for the release of OP 19, which is a member of the certified OP 14 family of products. OP 19’s official version is 14.19.1, which you may see in your software (such as in Help > About) and in the Help Center tabs labeled 14.19. You may also notice that the version number in content and videos may not match the version of your software, and some procedural content may not match the workflow in your software. We appreciate your patience and understanding as we make these enhancements.

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