Below are the database queries which are used to create this measure. These are run against a copy of the BSA prescribing data which we store in Google BigQuery. We're working on making our BigQuery tables publicly available at which point it will be possible to run and modify these queries yourself. But even where code and database queries are not directly useable by others we believe it is always preferable to make them public.
| Description | Opioid items with likely daily dose of ≥120mg morphine equivalence compared with prescribing of all items of these opioids |
|---|---|
| Why it matters | The Opioids Aware project seeks to improve prescribing of opioid analgesia. There is little evidence that opioids are helpful in long term pain, and the risk of harm increases significantly above 120mg morphine (or equivalent) per day, without much increase in benefit. We have assumed that if a patient is on regular doses of 120mg morphine a day or above that they are likely to have also had additional opioids for breakthrough pain. This is why we have set the threshold at ≥120mg morphine equivalence per day. The calculations are based on likely doses of long acting, regular opioids, for example morphine sulphate tablets or fentanyl patches. For example, we have assumed that MST 60mg tablets are “high dose”, as they are usually taken as one tablet twice daily (120mg daily dose), whereas MST 30mg are not, as the daily dose is 60mg. We have not included preparations used for breakthrough pain, e.g. Oramorph, or opioid injections which tend to be used more commonly in palliative care. We have calculated morphine equivalencies using the updated August 2020 tables available from the Faculty of Pain Medicine, Royal College of Anaesthetists. The NHS England National Medicines Optimisation Opportunities for 2023/24 identify reducing opioid use in chronic non-cancer pain as an area for improvement. |
| Tags | Standard, Opioids, Pain, National medicines optimisation opportunities, Safety |
| Implies cost savings | No |
| Authored by | richard.croker |
| Checked by | andrew.brown |
| Last reviewed | 2023-09-12 |
| Next review due | 2024-09-12 |
| History | View change history on GitHub → |
SELECT
CAST(month AS DATE) AS month,
practice AS practice_id,
SUM(p.items) AS numerator
FROM hscic.normalised_prescribing p INNER JOIN dmd.vmp vmp ON CONCAT(SUBSTR(p.bnf_code,0,9),'AA', SUBSTR(p.bnf_code,-2), SUBSTR(p.bnf_code,-2)) = vmp.bnf_code -- joins prescribing data to vmp table using generic BNF code
INNER JOIN dmd.vpi AS vpi ON vmp.id = vpi.vmp -- joins vmp to vpi table to get ingredient strengths (strnt_nmrtr_val)
INNER JOIN dmd.ont AS ont ON vmp.id = ont.vmp -- joins vmp to ont table to get formulation codes
INNER JOIN dmd.ontformroute AS route ON ont.form = route.cd -- joins ont table to ontform table to get formulation names
WHERE ((vpi.ing = 373492002 AND route.descr = 'patch.transdermal' AND strnt_nmrtr_val/(COALESCE(strnt_dnmtr_val,1)) >=50) --Fentanyl patches (strengths equal to or higher than 50mcg/hour) (ing code)
OR (vpi.ing = 387024006 AND route.descr LIKE '%modified-release.oral' AND p.bnf_name NOT LIKE '%Onexila%40mg%' AND strnt_nmrtr_val/(COALESCE(strnt_dnmtr_val,1)) >=40) --Oxycodone MR oral preps (strengths equal to or higher than 40mg) (ing code)
OR (vpi.ing IN (442699004,40755011000001109) AND route.descr LIKE '%modified-release.oral' AND strnt_nmrtr_val/(COALESCE(strnt_dnmtr_val,1)) >=150) --Tapentadol HCl/Phosphate MR oral preps (strengths equal to or higher than 150mg) (ing code)
OR (vpi.ing IN (387173000) AND route.descr LIKE 'patch.transdermal' AND strnt_nmrtr_val/(COALESCE(strnt_dnmtr_val,1)) >=52.5) --Buprenorphine patches (strengths equal to or higher than 52.5mg/hour) (ing code)
OR (vpi.ing IN (60886004) AND route.descr LIKE '%modified-release.oral' AND p.bnf_name NOT LIKE '%MXL%' AND strnt_nmrtr_val/(COALESCE(strnt_dnmtr_val,1)) >=60) --Morphine Sulfate MR oral preps [excluding MXL] (strengths equal to or higher than 60mg) (ing code)
OR (vpi.ing IN (60886004) AND route.descr LIKE '%modified-release.oral' AND p.bnf_name LIKE '%MXL%' AND strnt_nmrtr_val/(COALESCE(strnt_dnmtr_val,1)) >=120)) --MXL (strengths equal to or higher than 120mg) (ing code)
GROUP BY month, practice_idSELECT
CAST(month AS DATE) AS month,
practice AS practice_id,
SUM(p.items) AS denominator
FROM hscic.normalised_prescribing p INNER JOIN dmd.vmp vmp ON CONCAT(SUBSTR(p.bnf_code,0,9),'AA', SUBSTR(p.bnf_code,-2), SUBSTR(p.bnf_code,-2)) = vmp.bnf_code -- joins prescribing data to vmp table using generic BNF code
INNER JOIN dmd.vpi AS vpi ON vmp.id = vpi.vmp -- joins vmp to vpi table to get ingredient strengths (strnt_nmrtr_val)
INNER JOIN dmd.ont AS ont ON vmp.id = ont.vmp -- joins vmp to ont table to get formulation codes
INNER JOIN dmd.ontformroute AS route ON ont.form = route.cd -- joins ont table to ontform table to get formulation names
WHERE ((vpi.ing = 373492002 AND route.descr = 'patch.transdermal') --Fentanyl patches (ing code)
OR (vpi.ing = 387024006 AND route.descr LIKE '%modified-release.oral') --Oxycodone MR oral preps (ing code)
OR (vpi.ing IN (442699004,40755011000001109) AND route.descr LIKE '%modified-release.oral') --Tapentadol HCl/Phosphate MR oral preps (ing code)
OR (vpi.ing IN (387173000) AND route.descr LIKE 'patch.transdermal') --Buprenorphine patches (ing code)
OR (vpi.ing IN (60886004) AND route.descr LIKE '%modified-release.oral') --Morphine Sulfate MR oral preps (ing code)
OR (vpi.ing IN (387485001) AND route.descr LIKE '%modified-release.oral')) --Hydromorphone HCl MR oral preps (ing code)
GROUP BY month, practice_id