Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #31422

    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

    #33264

    Support
    Keymaster

    Hi Tony – thanks for your post.

    >> 1) Select particular staff based on their staff.knownas data field
    In your SQL source you starting with a “select staff.id”. In the datatable, this will result in a field called “id”, however ClinicOffice has no idea what a field called “id” refers to. If you change the start of the SQL to :-

    select staff.id, staff.id as staff_id, app.apptype_id...

    This will enable ClinicOffice to search on the STAFF member.

    >> 2) To have the option to include deleted staff on the selection list
    This will automatically be the case once you have made the above change, but you need to remove the “(staff.deleted<>true)” clause from your WHERE statement in the SQL.

    >> 3) Select by apptype_id
    It will already offer this based on your SQL.

    >> 4) Select by appointment status
    Add the following fields to your SQL :-

    app.appstatus_id, appstatus.name as _appstatus,

    Hope this helps!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.