 )
     BEGIN
 
+        -- all demographics for a patient by NHS number
         select
             p.id, p.nhs_number, p.date_of_birth, p.gender_concept_id,
             p.title, p.first_name, p.middle_names, p.last_name,

)
    BEGIN

        select
            i.patient_id, i.effective_date, i.original_code, i.original_term, i.original_code_scheme,
            pr.title as 'Clincian Title', pr.first_name as 'Clinician First Name', pr.last_name as 'Clinician Last Name'
        from
            pcr.immunisation i
            left join
            pcr.practitioner pr on pr.id = i.effective_practitioner_id
            join
            pcr.patient p on p.id = i.patient_id
        where
            p.nhs_number = _nhsno;

)
    BEGIN

        -- all registered patients who have an Asthma diagnosis code (68) and have been given Asthma medication (69) during the last 12 months
        select count(distinct(p.nhs_number)) as 'Asthma patients' from pcr.observation o
            join pcr.organisation org on org.id = o.owning_organisation_id
            join pcr.patient p on p.id = o.patient_id
            join pcr.medication_statement ms on ms.patient_id = p.id
            join pcr.gp_registration_status reg on reg.patient_id = p.id
        where
            org.ods_code = _odscode
        and
            o.original_code in (select read2_concept_id from subscriber_transform_pcr.code_set_codes where code_set_id in (68))
        and
            (ms.original_code in (select sct_concept_id from subscriber_transform_pcr.code_set_codes where code_set_id in (69))
        and
            ms.effective_date >= DATE(NOW() - INTERVAL 12 MONTH))
        and
            (reg.gp_registration_status_concept_id = 2 and reg.is_current = true)
        and
            p.date_of_death is null;

    END$$
DELIMITER ;
