Skip to content

Non standard measures

Jump to bottom
Peter Inglesby edited this page Apr 11, 2019 · 1 revision

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
Clone this wiki locally