| There are two versions of this SQL: Firebird and MySQL. The MySQL version only applies to clients who are have migrated to MySQL.. All other Practices should continue to use the Firebird version of this code. Click to expand and copy the code you need. If you are unsure which code to use, please check with your Practice Administrator. |
About
This report displays all primary Family Contacts (or Family Contacts with a sort of 1) and their information as entered into OP. Because parents can be associated with multiple children, the Authority and Children_Respectively fields are concatenated and list out the contact's authority with each specific child.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- This report simply displays information as it has been entered into OP. It does not verify the validity of the information.
- Each column can be filtered to display (BLANKS) to easily identify missing information.
- Blank fields indicate that the Contact record does not have an entry for that particular data item.
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
select MAX(CONTACT.FIRST_NAME) AS FIRST_NAME, MAX(CONTACT.LAST_NAME) AS LAST_NAME, MAX(CONTACT.HOME_PHONE) AS HOME_PHONE, MAX(CONTACT.WORK_PHONE) AS WORK_PHONE,
MAX(CONTACT.CELL_PHONE) AS CELL_PHONE, MAX(CONTACT.HOME_EMAIL) AS HOME_EMAIL, MAX(CONTACT.WORK_EMAIL) AS WORK_EMAIL,
MAX(CONTACT.PREF_REMINDER) as PREF_REMINDER, MAX(CONTACT.PREF_RECALL) as PREF_RECALL, MAX(CONTACT.PREF_PORTAL) as PREF_PORTAL,
MAX(CONTACT.PREF_GENERAL) as PREF_GENERAL,
(CODE_REL.DESCRIPT || ' (' || CODE_AUTH.DESCRIPT || ')' || ', ') AS AUTHORITY,
(REGISTER.FNAME || ' ' || REGISTER.LNAME || ' [' || (extract(month from REGISTER.BIRTHDAT) || '/' || extract(day from REGISTER.BIRTHDAT) || '/' || SUBSTRING(extract(year from REGISTER.BIRTHDAT) FROM 3 FOR 2) )|| ']' || ', ') AS CHILDREN_RESPECTIVELY
FROM CONTACT
INNER JOIN REGISTER_CONTACT ON CONTACT.ID = REGISTER_CONTACT.CONTACT_ID
INNER JOIN REGISTER ON REGISTER_CONTACT.PATNO = REGISTER.PATNO
LEFT OUTER JOIN CODE_TABLE CODE_REL ON REGISTER_CONTACT.CONTACT_ROLE = CODE_REL.STAFFNOTE
LEFT OUTER JOIN CODE_TABLE CODE_AUTH ON REGISTER_CONTACT.CONTACT_AUTHORITY = CODE_AUTH.STAFFNOTE
LEFT OUTER JOIN ADDRESSBOOK ON REGISTER.ADDR_ID = ADDRESSBOOK.ADDR_ID
where
CONTACT_ROLE IN ('FATHER', 'MOTHER', 'STEPFATHER', 'STEPMOTHER', 'GUARDIAN', 'CAREGIVER')
AND CONTACT.LAST_NAME NOT LIKE 'address change%'
AND REGISTER.STATUS_PAT = 'ACTIVE'
GROUP BY CONTACT.ID, code_rel.descript, code_auth.descript, register.lname, register.fname, register.birthdatSQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select MAX(CONTACT.FIRST_NAME) AS FIRST_NAME, MAX(CONTACT.LAST_NAME) AS LAST_NAME, MAX(CONTACT.HOME_PHONE) AS HOME_PHONE, MAX(CONTACT.WORK_PHONE) AS WORK_PHONE,
MAX(CONTACT.CELL_PHONE) AS CELL_PHONE, MAX(CONTACT.HOME_EMAIL) AS HOME_EMAIL, MAX(CONTACT.WORK_EMAIL) AS WORK_EMAIL,
MAX(CONTACT.PREF_REMINDER) as PREF_REMINDER, MAX(CONTACT.PREF_RECALL) as PREF_RECALL, MAX(CONTACT.PREF_PORTAL) as PREF_PORTAL,
MAX(CONTACT.PREF_GENERAL) as PREF_GENERAL,
group_concat(CODE_REL.DESCRIPT || ' (' || CODE_AUTH.DESCRIPT || ')' separator ', ') AS AUTHORITY,
group_concat(REGISTER.FNAME || ' ' || REGISTER.LNAME || ' [' || (extract(month from REGISTER.BIRTHDAT) || '/' || extract(day from REGISTER.BIRTHDAT) || '/' || SUBSTRING(extract(year from REGISTER.BIRTHDAT) FROM 3 FOR 2) )|| ']' separator ', ') AS CHILDREN_RESPECTIVELY
FROM CONTACT
INNER JOIN REGISTER_CONTACT ON CONTACT.ID = REGISTER_CONTACT.CONTACT_ID
INNER JOIN REGISTER ON REGISTER_CONTACT.PATNO = REGISTER.PATNO
LEFT OUTER JOIN CODE_TABLE CODE_REL ON REGISTER_CONTACT.CONTACT_ROLE = CODE_REL.STAFFNOTE
LEFT OUTER JOIN CODE_TABLE CODE_AUTH ON REGISTER_CONTACT.CONTACT_AUTHORITY = CODE_AUTH.STAFFNOTE
LEFT OUTER JOIN ADDRESSBOOK ON REGISTER.ADDR_ID = ADDRESSBOOK.ADDR_ID
where
CONTACT_ROLE IN ('FATHER', 'MOTHER', 'STEPFATHER', 'STEPMOTHER', 'GUARDIAN', 'CAREGIVER')
AND CONTACT.LAST_NAME NOT LIKE 'address change%'
AND REGISTER.STATUS_PAT = 'ACTIVE'
GROUP BY CONTACT.ID
