Which presentations have been prescribed which should not have been?

[sebbacon]

This finds all things prescribed in GP surgeries which are marked in DM&D as "invalid to prescribe in NHS primary care". There are about 6 such presentations a month.

36 describes a subset of this (blacklisted presentations).

WITH
  clipped_values AS (
  SELECT
    dmd.display_name,
    dmd.sched_1,
    dmd.dmdid,
    p.month,
    p.pct,
    p.bnf_code,
    bnf_name,
    quantity,
    net_cost,
    IEEE_DIVIDE(net_cost, quantity)
  FROM
    ebmdatalab.hscic.prescribing AS p
  INNER JOIN
    ebmdatalab.hscic.practices AS practices
  ON
    practices.code = p.practice
    AND practices.setting = 4
  INNER JOIN ( -- we only want one row per BNF code
    SELECT
      *
    FROM (
      SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY bnf_code ORDER BY dmdid ) AS row_num
      FROM
        ebmdatalab.hscic.dmd dmd
      WHERE
        pres_statcd = '2' ) AS ordered_dmd
    WHERE
      ordered_dmd.row_num = 1 ) dmd
  ON
    dmd.bnf_code = p.bnf_code
  WHERE
    month > TIMESTAMP('2015-12-01') )
SELECT
  month,
  pct,
  display_name,
  dmdid,
  bnf_code,
  COUNT(*) AS count,
  SUM(quantity) AS quantity
FROM
  clipped_values
GROUP BY
  month,
  pct,
  display_name,
  dmdid,
  bnf_code
ORDER BY
  month,
  count DESC

[sebbacon]

Sample of March 2017 results: