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 | Prescribing of high dose inhaled corticosteroids compared with prescribing of all inhaled corticosteroids |
|---|---|
| Why it matters | Latest BTS/SIGN guidance on the treatment of asthma recommends that patients should be maintained at the lowest possible dose of inhaled corticosteroid. Reduction in inhaled corticosteroid dose should be slow as patients deteriorate at different rates. Reductions should be considered every three months, decreasing the dose by approximately 25–50% each time. This measure uses table 12 of the BTS/SIGN guidance to define which inhalers are considered high-dose. We include inhalers in the measure where standard doses of the inhaler could administer high-doses of inhaled corticosteroids. The latest guidance for treatment of COPD now recommends use of another treatment in preference to inhaled corticosteroids. There is some evidence that inhaled corticosteroids increases the risk of pneumonia. This risk appears to increase with dose. |
| Tags | Standard, Respiratory, Safety |
| Implies cost savings | No |
| Authored by | christopher.wood |
| Checked by | richard.croker |
| Last reviewed | 2024-07-16 |
| Next review due | 2025-07-16 |
| 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 = 116575004 AND descr IN ('pressurizedinhalation.inhalation', 'powderinhalation.inhalation') -- 116575004 - Beclometasone dipropionate
AND ((strnt_nmrtr_val>=250) OR (strnt_nmrtr_val>=200 AND (p.bnf_code LIKE '0302000C0%CA' OR p.bnf_code LIKE '0302000C0%BZ')) -- beclometasone (extrafine)/formeterol - 0302000C0%CA 200/6 DPI, 0302000C0%BZ 200/6 MDI
OR (strnt_nmrtr_val>=172 AND (p.bnf_code LIKE '0301011AB%AB')))) -- 0301011AB%AB beclometasone (extrafine)/formeterol/glycopyrronium 200mcg beclometasone delivered)
OR (vpi.ing = 395726003 AND descr IN ('pressurizedinhalation.inhalation','powderinhalation.inhalation') AND strnt_nmrtr_val>=320) -- 395726003 - Budesonide
OR (vpi.ing = 417420004 AND descr IN ('pressurizedinhalation.inhalation','powderinhalation.inhalation') AND strnt_nmrtr_val>=160) -- 417420004 - Ciclesonide
OR (vpi.ing = 426409006 AND descr IN ('pressurizedinhalation.inhalation','powderinhalation.inhalation') AND strnt_nmrtr_val>=184) -- 426409006 - Fluticasone furoate
OR (vpi.ing = 396064000 AND ((descr = 'pressurizedinhalation.inhalation' AND strnt_nmrtr_val>=250) -- 396064000 - Fluticasone propionate
OR (descr = 'powderinhalation.inhalation' AND strnt_nmrtr_val>=500))) -- pMDI licensed 2 puffs BD, DPI licensed 1 puff BD
OR (vpi.ing = 395802006 AND descr IN ('pressurizedinhalation.inhalation','powderinhalation.inhalation') AND strnt_nmrtr_val>400) -- 395802006 - Mometasone furoate
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 in (116575004, 395726003, 417420004, 426409006, 396064000, 395802006) AND descr IN ('pressurizedinhalation.inhalation','powderinhalation.inhalation')
GROUP BY month, practice_id