You are at: NHSBSA Home Page | Freedom of Information
Previous Request List - Detail
Request Reference:
7951
Request Date:
17 Sep 2018
Request:
Dear BSA,
I would like to request
a)The number of patients who received Methotrexate 2.5mg tabs (1001030U0%AB) and Methotrexate 10mg (1001030U0%AC) in any one month period aggregated over the calendar year 2017 and presented at practice and CCG level.
b)
The number of Forms where the are items for Methotrexate 2.5mg tabs (1001030U0%AB) and Methotrexate 10mg (1001030U0%AC) on the same form, every month at every practice since August 2010
For each request I have drafted some SQL. I''m currently learning SQL so it may not be perfect and align wit your database structures but thought it might be helpful and make your workload a little bit less.
Thanks
[name redacted]
The number of patients who received Methotrexate 2.5mg tabs (1001030U0%AB) and Methotrexate 10mg (1001030U0%AC) in any one month period aggregated over the calendar year 2017
Suggested SQL
SELECT
ccg_code,
practice_code,
COUNT(distinct CASE WHEN mtx_2_5 > 0 and mtx_10 > 0 THEN patient_ID END) AS patients_both,
COUNT(distinct CASE WHEN mtx_2_5 > 0 and mtx_10 = 0 THEN patient_ID END) AS patients_2_5_only,
COUNT(distinct CASE WHEN mtx_2_5 = 0 and mtx_10 > 0 THEN patient_ID END) AS patients_10_only,
COUNT(distinct patient_id) AS patients_total
FROM (
SELECT
month,
ccg_code,
practice_code,
patient_id, -- Unsure how unidentified patients are account for in data structure, BSA will need to adjust
SUM(CASE WHEN SUBSTRING(bnf_code,1,13)=''1001030U0%AB''
THEN items END) AS mtx_2_5,
SUM(CASE WHEN SUBSTRING(bnf_code,1,13)=''1001030U0%AC''
THEN items END) AS mtx_10
FROM
underlying_source_table (epact2?)
WHERE
(bnf_code = ”1001030U0%AB” OR bnf_code = “1001030U0%AC”)
AND
year = 2017
GROUP BY
month,
ccg_code,
practice_code,
Patient_id )
GROUP BY
ccg_code,
practice_code
The number of Forms where the are items for Methotrexate 2.5mg tabs (1001030U0%AB) and Methotrexate 10mg (1001030U0%AC) every month at every practice since August 2010
Suggested SQL (this will output the number of mtx items on each form, divided into 2.5mg and 10mg dose sizes).
SELECT
month,
ccg_code,
practice_code,
form_number,
SUM(CASE WHEN SUBSTR(bnf_code,1,13)=''1001030U0%AB'' THEN items END) AS mtx_2_5,
SUM(CASE WHEN SUBSTR(bnf_code,1,13)=''1001030U0%AC'' THEN items END) AS mtx_10
FROM
underlying_source_table (ePACT2?)
WHERE
(bnf_code = ”1001030U0%AB” OR bnf_code = “1001030U0%AC”)
GROUP BY
month,
ccg_code,
practice_code,
Form_number --may need to be anonymised
Status: Complete
Status: Complete
Response Date:
12 Oct 2018