...
This queries the stats data that is used to populated 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.
| Code Block |
|---|
|
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
| Code Block |
|---|
|
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
| Code Block |
|---|
|
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 |