Viewing 1 post (of 1 total)
  • Author
    Posts
  • #31422
    clinicoffice_user
    Participant

      We have a nice simple report that shows the appointment count by staff and appointment type. Basically we just choose a date range for the appointments.

      The SQL Source from the grid report editor is:-
      select staff.id, staff.knownas, app.apptype_id, apptype.name as _apptype, cast(coalesce(count(app.id), 0) as int4) as appcount
      from staff
      left join app on (app.staff_id=staff.id)
      left join apptype on (app.apptype_id=apptype.id)
      left join appstatus on (app.appstatus_id=appstatus.id)
      where (staff.deleted<>true) and (appstatus.name not ilike ‘%cancel%’)
      group by staff.id, staff.knownas, apptype_id, apptype.name
      order by staff.knownas, _apptype

      SQL Source 2 is blank

      Options show:-
      Favourites=app.start;
      ForcedSearchFields=App Date|app.start|DateTime

      The report runs for all the current staff. We would like to be able to-

      1) Select particular staff based on their staff.knownas data field
      2) To have the option to include deleted staff on the selection list

      (I have some standard reports that do this but cannot see the SQL that does the job)

      3) Select by apptype_id

      4) Select by appointment status
      (in order to remove DNAs)

      Regards
      Tony Taylor
      ESO Maidstone

    Viewing 1 post (of 1 total)
    • You must be logged in to reply to this topic.