PatientView - Useful SQL

Usage Stats

This queries the stats data that is used to populate the graphs on the initial staff dashboard page to find the number of active users over total users for each group for a month. It is restricted to Unit type groups.

SELECT
  A.code,
  A.group_name,
  B.value,
  C.value
FROM
  pv_group A
LEFT JOIN pv_group_statistic B ON A.id = B.group_id AND B.type_id = 52 AND B.start_date = '2021-12-01'
LEFT JOIN pv_group_statistic C ON A.id = C.group_id AND C.type_id = 53 AND C.start_date = '2021-12-01'
WHERE
  A.type_id = 1;

Find Contact Details for all Groups

SELECT C.group_name
	,C.code
	,B.description
	,A.content
FROM pv_contact_point A
LEFT JOIN pv_lookup_value B ON A.type_id = B.id
LEFT JOIN pv_group C ON A.group_id = C.id
WHERE C.type_id = 1

Find all Unit Admins for Unit Groups

SELECT A.forename
	,A.surname
	,A.email
	,A.last_login
	,C.group_name
	,C.code
	,C.id
FROM pv_user A
INNER JOIN pv_user_group_role B ON A.id = B.user_id
LEFT JOIN pv_group C ON B.group_id = C.id
WHERE B.role_id = 2
	AND C.id <> 2