Hormones

[HelenCEBM]

all growth hormone usage for Croydon, Kingston, Merton, Richmond, Sutton and Wandsworth CCGs? from Jan 2016 to current

[HelenCEBM]

Asked user for more details

[HelenCEBM]

BNF Codes | Growth Hormone disorders Time Period (earliest date August 2010) | April 2016 to March 2017 Grouped monthly/yearly: | Yearly CCG/GP Practice(s) of Interest | by CCG Grouped by GP Practice or CCG? | CCG Include non-standard GP Practices? | No Include Actual Cost? | Yes Other Information | by growth hormone name. When do you need the data by? | 28/12/17 What organisation do you work for? | NEL CSU Organisation Type | NHS

[HelenCEBM]

-- customer enquiry growth hormones 2016-17 by CCG
-- SUM list sizes for each CCG, excluding non-standard practices  
WITH
  pop AS (
  SELECT stat.pct_id, stat.month, SUM(total_list_size) AS total_list_size
  FROM  ebmdatalab.hscic.practice_statistics stat
  LEFT JOIN ebmdatalab.hscic.practices prac   ON prac.code = stat.practice
  WHERE prac.setting = 4
  GROUP BY pct_id, month
  )

SELECT
    pct,
    b.chemical,
    '2016-17' AS year,
    SUM(items) AS items,
    SUM(quantity) AS quantity,
    SUM(actual_cost) AS actual_cost,
    AVG(total_list_size) AS avg_list_size
  FROM ebmdatalab.hscic.normalised_prescribing_standard p
  INNER JOIN pop  ON p.pct = pop.pct_id AND p.month = pop.month
  LEFT JOIN  ebmdatalab.hscic.bnf b  ON  p.bnf_code = b.presentation_code

  WHERE
    SUBSTR(bnf_code,1,9) IN ('0605010S0', '0605010Z0')
    AND p.month BETWEEN "2016-04-01" AND "2017-03-01"

 GROUP BY
    pct,
    chemical,
    year