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) of Anxiolytics and Hypnotics per 1000 patients|
|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 Average Daily Quantity (ADQ) of drug given per 1000 registered patients, 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 →|
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
SELECT 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