NHSBSA - Freedom of Information

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
Response Date: 12 Oct 2018
Response: Please see attached response

References: