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:
- 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.
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