Anxiolytics and Hypnotics: Average Daily Quantity per item

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 Number of average daily quantities (ADQs) per item for anxiolytics and hypnotics
Why it matters Anxiolytics and Hypnotics are drugs that are prescribed for short-term treatment of conditions such as anxiety and sleep problems. The Royal College of Psychiatrists states that "around 4 in every 10 people who take them every day for more than 6 weeks will become addicted" and therefore they should not be prescribed for longer than 4 weeks. This measure shows the mean Average Daily Quantity (ADQ) given per prescription, for both the older benzodiazepines, such as diazepam and temazepam, and the newer "Z-drugs", such as zopiclone. It excludes items that do not have an ADQ, such as melatonin.
Tags Standard, Central Nervous system, Mental Health, Safety
Implies cost savings No
History View change history on GitHub →

Numerator SQL

SELECT
     CAST(month AS DATE) AS month,
     practice AS practice_id,
     SUM(p.quantity * r.percent_of_adq) AS numerator
 FROM hscic.normalised_prescribing 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))
 WHERE p.bnf_code LIKE '0401%' AND --Hypnotics and Anxiolytics 
 p.bnf_code NOT LIKE '0401010AC%' AND   --Sodium Oxybate 
 p.bnf_code NOT LIKE '0401010AD%' AND   --Melatonin 
 p.bnf_code NOT LIKE '0401020K0%AD' AND --Diazepam_Soln 5mg/2.5ml Rectal Tube 
 p.bnf_code NOT LIKE '0401020K0%AE' AND --Diazepam_Soln 10mg/2.5ml Rectal Tube 
 p.bnf_code NOT LIKE '0401020K0%BQ'     --Diazepam_Soln 2.5mg/1.25ml Rectal Tube
 GROUP BY month, practice_id

Denominator SQL

SELECT
     CAST(month AS DATE) AS month,
     practice AS practice_id,
     SUM(items) AS denominator
 FROM hscic.normalised_prescribing p
 WHERE p.bnf_code LIKE '0401%' AND --Hypnotics and Anxiolytics 
 p.bnf_code NOT LIKE '0401010AC%' AND   --Sodium Oxybate 
 p.bnf_code NOT LIKE '0401010AD%' AND   --Melatonin 
 p.bnf_code NOT LIKE '0401020K0%AD' AND --Diazepam_Soln 5mg/2.5ml Rectal Tube 
 p.bnf_code NOT LIKE '0401020K0%AE' AND --Diazepam_Soln 10mg/2.5ml Rectal Tube 
 p.bnf_code NOT LIKE '0401020K0%BQ'     --Diazepam_Soln 2.5mg/1.25ml Rectal Tube
 GROUP BY month, practice_id