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 | Opioids with likely daily dose of ≥90mg morphine equivalence per 1000 patients |
|---|---|
| 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 90mg morphine (or equivalent) per day (recently reduced from 120mg), without much increase in benefit. We have assumed that if a patient is on regular doses of 90mg 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 ≥90mg 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 Longtec 30mg tablets are “high dose”, as they are usually taken as one tablet twice daily (60mg daily dose, equivalent to 90mg of oral morphine), whereas Longtec 20mg are not, as the daily dose is 40mg (60mg of oral morphine). 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 2024/25 identify reducing opioid use in chronic non-cancer pain as an area for improvement. |
| Tags | Standard, Opioids, National medicines optimisation opportunities, Pain, Safety |
| Implies cost savings | No |
| Authored by | andrew.brown |
| Checked by | richard.croker |
| Last reviewed | 2025-11-17 |
| Next review due | 2027-11-17 |
| History | View change history on GitHub → |
| Change log |
2017-02-20: Created measure 2025-11-17: Reduce threshold for 'high dose' from 120mg OME to 90mg OME |
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 ((COALESCE(vpi.bs_subid,vpi.ing) = 373492002 AND route.descr = 'patch.transdermal' AND strnt_nmrtr_val/(COALESCE(strnt_dnmtr_val,1)) >=37.5) --Fentanyl patches (strengths equal to or higher than 37.5mcg/hour) (ing code)
OR (COALESCE(vpi.bs_subid,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)) >=30) --Oxycodone MR oral preps [excluding Onexila 40mg as 24hr] (strengths equal to or higher than 30mg) (ing code)
OR (COALESCE(vpi.bs_subid,vpi.ing) = 441757005 AND route.descr LIKE '%modified-release.oral' AND strnt_nmrtr_val/(COALESCE(strnt_dnmtr_val,1)) >=112.5) --Tapentadol base substance (strengths equal to or higher than 112.5mg) (ing code)
OR (COALESCE(vpi.bs_subid,vpi.ing) = 387173000 AND route.descr LIKE 'patch.transdermal' AND strnt_nmrtr_val/(COALESCE(strnt_dnmtr_val,1)) >=39.375) --Buprenorphine patches (strengths equal to or higher than 39.375mg/hour) (ing code)
OR (COALESCE(vpi.bs_subid,vpi.ing) = 60886004 AND route.descr LIKE '%modified-release.oral' AND p.bnf_name NOT LIKE '%MXL%' AND strnt_nmrtr_val/(COALESCE(strnt_dnmtr_val,1)) >=45) --Morphine Sulfate MR oral preps [excluding MXL as 24hr] (strengths equal to or higher than 45mg) (ing code)
OR (COALESCE(vpi.bs_subid,vpi.ing) = 60886004 AND route.descr LIKE '%modified-release.oral' AND p.bnf_name LIKE '%MXL%' AND strnt_nmrtr_val/(COALESCE(strnt_dnmtr_val,1)) >=90) --MXL (strengths equal to or higher than 90mg) (ing code)
OR (COALESCE(vpi.bs_subid,vpi.ing) IN (44508008, 387485001) AND route.descr LIKE '%modified-release.oral' AND strnt_nmrtr_val/(COALESCE(strnt_dnmtr_val,1)) >=9)) --Hydromorphone base substance (strengths equal to or higher than 9mg) (ing code)
GROUP BY month, practice_idSELECT
CAST(month AS DATE) AS month,
practice AS practice_id,
SUM(total_list_size / 1000.0) AS denominator
FROM hscic.practice_statistics
GROUP BY month, practice_id