Useful PKB SQL

Sent Test Results

This query against the UKRDC shows which PV tests, as converted to LOINC, have been sent recently by a particular unit.

SELECT DISTINCT
  CASE WHEN destination_code IS NULL
    THEN serviceidcode
    ELSE destination_code
  END AS Code,
  E.description
FROM
  resultitem A
INNER JOIN laborder B ON A.orderid = B.id
INNER JOIN patientrecord C ON B.pid = C.pid
LEFT JOIN code_map D ON A.serviceidcode = source_code AND source_coding_standard = 'PV' and destination_coding_standard = 'LOINC'
LEFT JOIN code_list E ON E.coding_standard = D.destination_coding_standard AND E.code = D.destination_code
WHERE
  A.observationtime >= '2020-01-01' AND
  C.sendingfacility = 'RCB55' AND
  C.sendingextract = 'PV';