Non standard measures - bennettoxford/openprescribing GitHub Wiki

The following measure definitions are non-standard.

• aafpercent

  • presentations / presentations
  • lists of BNF codes for both numerator and denominator derived from data in table curated by Rich at measures.cmpa_products
  • numerator_from, denominator_from
    • {hscic}.normalised_prescribing_standard p LEFT JOIN {measures}.cmpa_products r ON p.bnf_code=r.bnf_code
  • numerator_where
    • r.type = 'AAF' --this filters to only products listed as 'AAF' in the products table
  • denominator_where
    • r.type != 'exclude' --this filters to all products not listed as 'excluded' in the products table'

• bdz_adq

  • weighted presentations / presentations
  • weighting defined (for generic presentations only, so LEFT JOIN is a bit odd) in hscic.bdz_adq
  • numerator_columns
    • SUM(p.quantity * r.percent_of_adq)
  • numerator_from
    • {hscic}.normalised_prescribing_standard p LEFT JOIN {hscic}.bdz_adq r ON concat(substr(p.bnf_code,0,9),substr(p.bnf_code,-2)) = concat(substr(r.bnf_code,0,9),substr(r.bnf_code,-2))

• bdzper1000

  • weighted presentations / 1000 patients
  • as for bdz_adq

• ghost_generic_measure

  • This does something very different from other measures, with most of the work done in vw__ghost_generic_measure
  • numerator_columns
    • SUM(possible_savings) AS numerator
  • numerator_from
    • {measures}.vw__ghost_generic_measure
  • numerator_where
    • (possible_savings >= 2 OR possible_savings <=-2)
  • denominator_columns
    • SUM(net_cost) AS denominator
  • denominator_from
    • {measures}.vw__ghost_generic_measure

• ktt9_uti_antibiotics

  • weighted presentations / presentations
  • weighting defined as a column on the presentation table
  • numerator_columns
    • SUM(p.quantity * r.adq_per_quantity) AS numerator
  • numerator_from
    • {hscic}.normalised_prescribing_standard p LEFT JOIN {hscic}.presentation r ON p.bnf_code = r.bnf_code

• lpherbal

  • presentations / 1000 patients
  • list of BNF codes for numerator derived from data in table curated by Rich at richard.herbal_list
  • This uses a list of presentations defined in richard.herbal_list
  • numerator_from
    • {hscic}.normalised_prescribing_standard p INNER JOIN (SELECT DISTINCT bnf_code FROM ebmdatalab.richard.herbal_list) r ON p.bnf_code = r.bnf_code

• lpneedles, test_strip

  • presentations, varying by month / 1000 patients
  • table of BNF codes for each month defined in measures.vw__median_price_per_unit
  • numerator_from
    • {hscic}.normalised_prescribing_standard p LEFT JOIN {measures}.vw__median_price_per_unit r ON p.month=r.date AND p.bnf_code = r.bnf_code
  • numerator_where
    • ... AND r.median_price_per_unit >= 0.05 (lpneedles)
    • ... AND r.median_price_per_unit >= 0.02 (test_strip)

• opioideome

  • weighted presentations / 1000 patients
  • weighting defined in calculation in measures.opioide_total_ome
  • numerator_columns
    • SUM(total_ome) AS numerator
  • numerator_from
    • {measures}.opioid_total_ome

• pregabalinmg

  • weighted presentations / 1000 patients
  • weighting and list of BNF codes both defined in calculation in measures.pregabalin_total_mg
  • numerator_columns
    • SUM(lyrica_mg) AS numerator
  • numerator_from
    • {measures}.pregabalin_total_mg

• tamoxifen

  • weighted presentations / number of female patients over 35
  • weightings defined in the numerator_columns attribute
  • numerator_columns
    • SUM(quantity/ CASE WHEN bnf_name LIKE '%Oral%' THEN 10 WHEN bnf_name LIKE '%Liq%' THEN 10 WHEN RTRIM(bnf_name) LIKE '%10mg' THEN 2 WHEN RTRIM(bnf_name) LIKE '%40mg' THEN 0.5 ELSE 1 END)
  • numerator_from
    • {hscic}.normalised_prescribing_standard
  • denominator_columns
    • (max(female_35_44) + max(female_45_54) + max(female_55_64) + max(female_65_74)) / 1000.0 AS denominator
  • denominator_from
    • {hscic}.practice_statistics