SQL Snippets

These sections contain code you can copy and paste when creating SQL reports in Communicare.

Useful information

When the report selects patient names, use:

SELECT P.FULL_NAME_AGE_TITLE_TODAY
FROM PATIENT P
WHERE (P.CURRENT_STATUS not in ('SF', 'SNP'))

If you need to include deceased patients then the WHERE clause should be:
```
WHERE ((P.CURRENT_STATUS not in ('SF', 'SNP'))
  OR (P.CURRENT_STATUS IS NULL))
```

When the report selects services, use:

SELECT PE.SEQUENCE_DATE

FROM PAT_ENCOUNTER PE

WHERE (PE.SEQUENCE_DATE >= :First_date_to_report)
AND (PE.SEQUENCE_DATE - 1 < :Last_date_to_report)
AND (PE.PAT_ENC_STAT in ('S','F','P'))

Finding current patients living in the HSA between two ages today

select count(*)

from patient p

join (locality l
join locality_combine_locals lcl on l.locality_no = lcl.locality_no
join locality_combine lc on lcl.locality_combine_no = lc.locality_combine_no
  and lc.locality_combine_desc_uc = 'HEALTH SERVICE AREA') on p.locality_no = l.locality_no

where p.current_status = 'SC'
and p.age_birthyears_today between :Lower_age and :Upper_age

Finding current patients living in the HSA between two ages at a specified time

select count(*)

from patient p
join (pat_group_member pgm
join pat_group pg on pg.pat_grp_no = pgm.pat_grp_no
  and pg.sys_code = 'SC'
  and pgm.join_date <= :Reference_date
  and (pgm.exit_date > :Reference_date
    or pgm.exit_date is null)) on pgm.pat_id = p.pat_id
join (pat_address pa
join locality l on pa.locality_no = l.locality_no
   and pa.home_indic = 'Y'
   and pa.from_date <= :Reference_date
   and (pa.to_date >= :Reference_date
     or pa.to_date is null)
join locality_combine_locals lcl on l.locality_no = lcl.locality_no
join locality_combine lc on lcl.locality_combine_no = lc.locality_combine_no
  and lc.locality_combine_desc_uc = 'HEALTH SERVICE AREA') on p.pat_id = pa.pat_id

where cu_agebirthyears(p.date_of_birth,:Reference_date) between :Lower_age and :Upper_age

Adding known aliases as a single field

Copy this into the select statement, replacing the alias p (as in p.pat_id) if the patient table has a different alias in your query:

(select list(x.family_name || ', ' || f_lrtrim(x.pat_forenames), '; ')
 from patient_alias x
 where x.pat_id = p.pat_id
 and x.current_alias_indicator = 'N')

For QueryBuilder users:

Open your query and go to Tables > Calculated fields.

Add a new field called aliases and copy the following into the definition field:

(select list(x.family_name || ', ' || f_lrtrim(x.pat_forenames), '; ') 
from patient_alias x where x.pat_id = T8.pat_id and x.current_alias_indicator = 'N')

Click OK
Select the PATIENT table as the attachment table.
Click OK.
Select Show > Adjust virtual space. You will see a new field at the bottom of the patient table called aliases. Use it as a regular database field and it will show all aliases separated by semicolons.