Versions Compared

Version Old Version 3 New Version Current
Changes made by

George Swinnerton

George Swinnerton

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
languagesql
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
languagesql
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
languagesql
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