About

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.

SQL Code: Firebird

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

Copy

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

SQL Code: MySQL

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

Copy

select count(su) as total_visits, rm_num from
(
select distinct schedule_pk_uniqkey as su, rm.colloquial_name as rm_num, 
rm.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

)  t1 group by rm_num
order by 1