Which blacklisted presentations have been prescribed?

[sebbacon]

Products are blacklisted at AMPP level, so (for example) 15g Daktarin 2% cream is blacklisted whereas 30g is not. This query does not distinguish between those as our data has nothing to say about pack sizes.

WITH
  clipped_values AS (
  SELECT
    dmd.display_name,
    dmd.sched_1,
    dmd.dmdid,
    p.practice,
    p.bnf_code,
    bnf_name,
    quantity,
    net_cost,
    IEEE_DIVIDE(net_cost, quantity)
  FROM
    ebmdatalab.hscic.prescribing AS p
  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 sched_1 = 't'
    ) as ordered_dmd
    where ordered_dmd.row_num = 1
  ) dmd
  ON
    dmd.bnf_code = p.bnf_code
  WHERE
    p.month = TIMESTAMP('2016-09-01'))
SELECT
  display_name, dmdid, bnf_code, count(*) as count, sum(quantity) as quantity
FROM
  clipped_values
group by display_name, dmdid, bnf_code
order by count desc

[sebbacon]

Most of the blacklisted things here will have been dispensed because they were prescribed generically, and the generic presentation is not blacklisted. For example Librium 5mg capsules (Meda Pharmaceuticals Ltd) are blacklisted but Chlordiazepoxide 5mg capsules (the generic) are not.

It seems odd that things like this would be blacklisted as Chlordiazepoxide 5mg capsules is already in Category A of the DT so the price is set anyway.

[sebbacon]

We can further restrict the query above to only generics. This will then give us a list of things that really should never be prescribed. This version breaks it down by month since Jan 2016 (note this is using a blacklist current as of Jan 2017 so should be treated with caution):

WITH
  clipped_values AS (
  SELECT
    dmd.display_name,
    dmd.sched_1,
    dmd.dmdid,
    p.month,
    p.practice,
    p.bnf_code,
    bnf_name,
    quantity,
    net_cost,
    IEEE_DIVIDE(net_cost, quantity)
  FROM
    ebmdatalab.hscic.prescribing AS p
  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
        sched_1 = 't'
        AND product_type IN ('1',
          '3') ) 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,
  practice,
  display_name,
  dmdid,
  bnf_code,
  COUNT(*) AS count,
  SUM(quantity) AS quantity
FROM
  clipped_values
GROUP BY
  month,
  practice,
  display_name,
  dmdid,
  bnf_code
ORDER BY
  month,
  count DESC