Subject Status Details Query

This query is to generate detailed subject status report that takes “Study Name” as input parameter and provides information of the subjects related to the study.
Output of the query consists of these information:
Ranking number (Increment Number for each row)
Screening Number of the Subject
Cohort Number (Name of the Cohort)
Screening Date of the subject
Eligibility of the subject after screening
Subject randomisation number
Re screening number
Signed date of “Informed Consent”
Race of the volunteer
Year of birth of volunteer
Sex of volunteer
Eligibility result
Subject status
Item name of all exclusion criteria of the subject
Item name of all inclusion criteria of the subject
Date of first dose
Date of last dose
All study events visited by the subject
Last visit date
SET @row_number = 0;
SET @study_id = (SElECT id FROM study WHERE name = '<STUDY_NAME>');
SElECT 
(@row_number := @row_number + 1) AS 'Ranking Number',
subject.screening_number AS 'Screening Number',
cohort.name AS 'Cohort Number',
CASE WHEN screening_dates.screeningDate IS NULL THEN '' ELSE DATE_FORMAT(screening_dates.screeningDate,'%d-%b-%Y') END AS 'SCR Date',
CASE WHEN eligibleAfterScreening.auditSubjectId IS NULL THEN 'No' ELSE 'Yes' END AS 'Eligible After Screening',
CASE WHEN subject.randomization_number IS NULL THEN '-' ELSE subject.randomization_number END AS 'Randomization/Assignment Number',
CASE WHEN reScreen.rescreenNumber IS NULL THEN 'No' ELSE reScreen.rescreenNumber END AS 'Re-Screen',
first_ifc_date.firstIcfDate  AS 'Date of IC Signed',
races.race_name AS 'Race',
year(volunteer.date_of_birth) AS 'Year of Birth',
CASE WHEN volunteer.gender_male = true THEN 'M' ELSE 'F' END AS 'Sex',
CASE WHEN eligibilityResult.eligibilityResultValue IS NULL THEN '' ELSE eligibilityResult.eligibilityResultValue END AS 'Subject Eligibility Result',
subject.subject_study_status AS 'Subject Status',
CASE WHEN exclusionCriteria.itemName IS NULL THEN '' ELSE exclusionCriteria.itemName END AS 'Excl. Criteria',
CASE WHEN inclusionCriteria.itemName IS NULL THEN '' ELSE inclusionCriteria.itemName END AS 'Inc. Criteria',
CASE WHEN minSubjectDose.doseDate IS NULL THEN '' ELSE DATE_FORMAT(minSubjectDose.doseDate,'%d-%b-%Y') END AS 'First Dosing',
CASE WHEN maxSubjectDose.doseDate IS NULL THEN '' ELSE DATE_FORMAT(maxSubjectDose.doseDate,'%d-%b-%Y') END AS 'Last Dosing',
CASE WHEN subjectStudies.visitName IS NULL THEN '' ELSE subjectStudies.visitName END AS 'Study Event',
CASE WHEN subjectLatestVisitDate.latestVisitDate IS NULL THEN '' ELSE DATE_FORMAT(subjectLatestVisitDate.latestVisitDate,'%d-%b-%Y') END AS 'Attended EOS/FUP'
FROM cohort_assignment
    INNER JOIN study_site ON study_site.id  = cohort_assignment.study_site_id
    INNER JOIN cohort ON cohort.id  = cohort_assignment.cohort_id
    INNER JOIN epoch ON epoch.id = cohort.epoch_id
    INNER JOIN study ON study.id = epoch.study_id
    INNER JOIN subject ON subject.id = cohort_assignment.subject_id
    INNER JOIN volunteer ON volunteer.id = subject.volunteer_id
    LEFT JOIN (SELECT sar.subject_id, DATE_FORMAT(MIN(sar.primary_informed_consent_date),'%d-%b-%Y') AS firstIcfDate
        from subject_audit_record sar
        INNER JOIN subject s ON s.id = sar.subject_id
        INNER JOIN cohort_assignment ca ON ca.subject_id = s.id
        INNER JOIN cohort c ON c.id = ca.cohort_id
        INNER JOIN epoch e ON e.id = c.epoch_id
        INNER JOIN study st ON st.id = e.study_id
        WHERE st.id = @study_id AND sar.primary_informed_consent_date  IS NOT NULL
        GROUP BY sar.subject_id) AS first_ifc_date ON first_ifc_date.subject_id = subject.id
    LEFT JOIN (SELECT GROUP_CONCAT(DISTINCT subject.screening_number) AS 'rescreenNumber', volunteer.id AS volunteerId
        FROM cohort_assignment                     
        INNER JOIN study_site ON study_site.id  = cohort_assignment.study_site_id                     
        INNER JOIN cohort ON cohort.id  = cohort_assignment.cohort_id                     
        INNER JOIN epoch ON epoch.id = cohort.epoch_id                     
        INNER JOIN study ON study.id = epoch.study_id                     
        INNER JOIN subject ON subject.id = cohort_assignment.subject_id                     
        INNER JOIN volunteer ON volunteer.id = subject.volunteer_id                     
        WHERE study.id = @study_id GROUP BY volunteerId) AS reScreen ON reScreen.volunteerId = volunteer.id 
    LEfT JOIN (SELECT GROUP_CONCAT(DISTINCT volunteer_race.name separator ', ') AS race_name, volunteer.id AS volunteerId
      from study_event
      INNER JOIN study_event_data sed ON sed.study_event_id = study_event.id
      INNER JOIN cohort_assignment ON cohort_assignment.id = sed.cohort_assignment_id
      INNER JOIN cohort ON cohort_id = cohort_assignment.cohort_id
      INNER JOIN epoch ON epoch.id = cohort.epoch_id
      INNER JOIN study ON study.id = epoch.study_id
      INNER JOIN subject ON subject.id = cohort_assignment.subject_id
      INNER JOIN volunteer ON volunteer.id = subject.volunteer_id
      INNER JOIN volunteer_volunteer_race ON volunteer_volunteer_race.volunteer_volunteer_races_id = volunteer.id
      INNER JOIN volunteer_race ON volunteer_race.id = volunteer_volunteer_race.volunteer_race_id
      WHERE study.id = @study_id GROUP BY volunteer.id) AS races ON races.volunteerId = volunteer.id
    LEFT JOIN (SELECT subject.id AS screeningSubjectId, date_value AS 'screeningDate' FROM item_data id 
      INNER JOIN item_ref ir ON id.item_ref_id = ir.id 
      INNER JOIN item ON ir.item_id = item.id 
      INNER JOIN item_alias ON item_alias.item_aliases_id = item.id
      INNER JOIN alias ON alias.id = item_alias.alias_id
      INNER JOIN item_group_data igd ON id.item_group_data_id = igd.id
      INNER JOIN form_data fd ON igd.form_data_id = fd.id
      INNER JOIN study_event_data sed ON fd.study_event_data_id = sed.id
      INNER JOIN subject ON sed.subject_id = subject.id
      INNER JOIN scheduled_activity sa ON fd.scheduled_activity_id = sa.id
      INNER JOIN cohort_assignment ca ON sed.cohort_assignment_id = ca.id
      INNER JOIN cohort ON ca.cohort_id = cohort.id
      INNER JOIN epoch ON epoch.id = cohort.epoch_id
      INNER JOIN study ON subject.study_id = study.id
      INNER JOIN form ON sa.form_id = form.id
      WHERE study.id = @study_id
      AND alias.name = 'Date of Visit' AND alias.context = 'Date of Visit'
      AND cohort.subject_number_action = 'SCREENING') AS screening_dates ON screening_dates.screeningSubjectId = subject.id 
    LEFT JOIN (SELECT subject.id AS auditSubjectId  
      FROM audit_record 
      INNER JOIN item_data_audit_record ON item_data_audit_record.audit_record_id = audit_record.id 
      INNER JOIN item_data id ON id.id = item_data_audit_record.item_data_audit_records_id
      INNER JOIN item_ref ON item_ref.id = id.item_ref_id
      INNER JOIN item ON item.id = item_ref.item_id
      INNER JOIN item_group_data igd ON igd.id = id.item_group_data_id
      INNER JOIN form_data fd ON fd.id = igd.form_data_id
      INNER JOIN study_event_data ON study_event_data.id = fd.study_event_data_id
      INNER JOIN subject ON subject.id = study_event_data.subject_id
      INNER JOIN study ON study.id = subject.study_id
      INNER JOIN cohort_assignment ca ON study_event_data.cohort_assignment_id = ca.id
      INNER JOIN cohort ON ca.cohort_id = cohort.id
      INNER JOIN epoch ON epoch.id = cohort.epoch_id
      WHERE study.id = @study_id
      AND item.base_study_item_type = 'EligibilityResult'
      AND audit_record.value = 'Eligible at Screening' ) AS eligibleAfterScreening ON eligibleAfterScreening.auditSubjectId = subject.id
    LEFT JOIN (SELECT subject.id AS eligibilitySubjectId, id.value AS eligibilityResultValue
      FROM item_data id
      INNER JOIN item_ref ON item_ref.id = id.item_ref_id
      INNER JOIN item ON item.id = item_ref.item_id
      INNER JOIN item_group_data igd ON igd.id = id.item_group_data_id
      INNER JOIN form_data fd ON fd.id = igd.form_data_id
      INNER JOIN study_event_data ON study_event_data.id = fd.study_event_data_id
      INNER JOIN subject ON subject.id = study_event_data.subject_id
      INNER JOIN study ON study.id = subject.study_id
      INNER JOIN cohort_assignment ca ON study_event_data.cohort_assignment_id = ca.id
      INNER JOIN cohort ON ca.cohort_id = cohort.id
      INNER JOIN epoch ON epoch.id = cohort.epoch_id
      WHERE study.id = @study_id
      AND item.base_study_item_type = 'EligibilityResult') AS eligibilityResult ON eligibilityResult.eligibilitySubjectId = subject.id
    LEfT join (SELECT subject.id AS exclusionSubjectId, GROUP_CONCAT(item.name separator ', ') AS itemName
      FROM item_data id
      INNER JOIN code_list_item ON code_list_item.id = id.code_list_item_id
      INNER JOIN item_ref ON item_ref.id = id.item_ref_id
      INNER JOIN item ON item.id = item_ref.item_id
      INNER JOIN item_group_data igd ON igd.id = id.item_group_data_id
      INNER JOIN form_data fd ON fd.id = igd.form_data_id
      INNER JOIN study_event_data ON study_event_data.id = fd.study_event_data_id
      INNER JOIN subject ON subject.id = study_event_data.subject_id
      INNER JOIN study ON study.id = subject.study_id
      INNER JOIN cohort_assignment ca ON study_event_data.cohort_assignment_id = ca.id
      INNER JOIN cohort ON ca.cohort_id = cohort.id
      INNER JOIN epoch ON epoch.id = cohort.epoch_id
      WHERE study.id = @study_id
      AND item.base_study_item_type = 'EXCLUSION_ITEM' AND code_list_item.coded_value = 'MET'
      GROUP BY exclusionSubjectId) AS exclusionCriteria ON exclusionCriteria.exclusionSubjectId = subject.id
    LEfT join (SELECT subject.id AS inclusionSubjectId, GROUP_CONCAT(item.name separator ', ') AS itemName
      FROM item_data id
      INNER JOIN code_list_item ON code_list_item.id = id.code_list_item_id
      INNER JOIN item_ref ON item_ref.id = id.item_ref_id
      INNER JOIN item ON item.id = item_ref.item_id
      INNER JOIN item_group_data igd ON igd.id = id.item_group_data_id
      INNER JOIN form_data fd ON fd.id = igd.form_data_id
      INNER JOIN study_event_data ON study_event_data.id = fd.study_event_data_id
      INNER JOIN subject ON subject.id = study_event_data.subject_id
      INNER JOIN study ON study.id = subject.study_id
      INNER JOIN cohort_assignment ca ON study_event_data.cohort_assignment_id = ca.id
      INNER JOIN cohort ON ca.cohort_id = cohort.id
      INNER JOIN epoch ON epoch.id = cohort.epoch_id
      WHERE study.id = @study_id
      AND item.base_study_item_type = 'INCLUSION_ITEM' AND code_list_item.coded_value = 'NOTMET'
      GROUP BY inclusionSubjectId) AS inclusionCriteria ON inclusionCriteria.inclusionSubjectId = subject.id
    LEfT join (SELECT subject.id AS minDoseSubjectId, min(id.date_value) AS doseDate
      FROM item_data id
      INNER JOIN item_ref ON item_ref.id = id.item_ref_id
      INNER JOIN item ON item.id = item_ref.item_id
      INNER JOIN item_alias ai ON ai.item_aliases_id = item.id
      INNER JOIN alias a ON a.id = ai.alias_id
      INNER JOIN item_group_data igd ON igd.id = id.item_group_data_id
      INNER JOIN form_data fd ON fd.id = igd.form_data_id
      INNER JOIN study_event_data ON study_event_data.id = fd.study_event_data_id
      INNER JOIN subject ON subject.id = study_event_data.subject_id
      INNER JOIN study ON study.id = subject.study_id
      INNER JOIN cohort_assignment ca ON study_event_data.cohort_assignment_id = ca.id
      INNER JOIN cohort ON ca.cohort_id = cohort.id
      INNER JOIN epoch ON epoch.id = cohort.epoch_id
      WHERE study.id = @study_id
      AND a.name='Dose' AND a.context='Dose' AND date_value IS NOT NULL
      GROUP BY minDoseSubjectId) AS minSubjectDose ON minSubjectDose.minDoseSubjectId = subject.id
    LEfT join (SELECT subject.id AS maxDoseSubjectId, max(id.date_value) AS doseDate
      FROM item_data id
      INNER JOIN item_ref ON item_ref.id = id.item_ref_id
      INNER JOIN item ON item.id = item_ref.item_id
      INNER JOIN item_alias ai ON ai.item_aliases_id = item.id
      INNER JOIN alias a ON a.id = ai.alias_id
      INNER JOIN item_group_data igd ON igd.id = id.item_group_data_id
      INNER JOIN form_data fd ON fd.id = igd.form_data_id
      INNER JOIN study_event_data ON study_event_data.id = fd.study_event_data_id
      INNER JOIN subject ON subject.id = study_event_data.subject_id
      INNER JOIN study ON study.id = subject.study_id
      INNER JOIN cohort_assignment ca ON study_event_data.cohort_assignment_id = ca.id
      INNER JOIN cohort ON ca.cohort_id = cohort.id
      INNER JOIN epoch ON epoch.id = cohort.epoch_id
      WHERE study.id = @study_id
      AND a.name='Dose' AND a.context='Dose' AND date_value IS NOT NULL
      GROUP BY maxDoseSubjectId) AS maxSubjectDose ON maxSubjectDose.maxDoseSubjectId = subject.id
    LEFT JOIN ( SELECT study_inner.subjectId AS subjectId, GROUP_CONCAT(study_inner.visitDateName separator ', ') AS visitName from (
      (SELECT subject.id AS subjectId, CONCAT(se.name, ' ', DATE_FORMAT(id.date_value,'%d-%b-%Y')) AS 'visitDateName'
      FROM item_data id 
      INNER JOIN item_ref ir ON id.item_ref_id = ir.id 
      INNER JOIN item ON ir.item_id = item.id 
      INNER JOIN item_alias ON item_alias.item_aliases_id = item.id
      INNER JOIN alias ON alias.id = item_alias.alias_id
      INNER JOIN item_group_data igd ON id.item_group_data_id = igd.id
      INNER JOIN form_data fd ON igd.form_data_id = fd.id
      INNER JOIN study_event_data sed ON fd.study_event_data_id = sed.id
      INNER JOIN study_event se ON se.id = sed.study_event_id
      INNER JOIN subject ON sed.subject_id = subject.id
      INNER JOIN scheduled_activity sa ON fd.scheduled_activity_id = sa.id
      INNER JOIN cohort_assignment ca ON sed.cohort_assignment_id = ca.id
      INNER JOIN cohort ON ca.cohort_id = cohort.id
      INNER JOIN epoch ON epoch.id = cohort.epoch_id
      INNER JOIN study ON subject.study_id = study.id
      INNER JOIN form ON sa.form_id = form.id
      WHERE study.id = @study_id
      AND alias.name = 'Date of Visit' AND alias.context = 'Date of Visit' AND id.date_value IS NOT NULL) AS study_inner)
      GROUP BY subjectId) AS subjectStudies ON subjectStudies.subjectId = subject.id
    LEFT JOIN (SELECT subject.id AS subjectId, max(id.date_value) AS 'latestVisitDate'
      FROM item_data id 
      INNER JOIN item_ref ir ON id.item_ref_id = ir.id 
      INNER JOIN item ON ir.item_id = item.id 
      INNER JOIN item_alias ON item_alias.item_aliases_id = item.id
      INNER JOIN alias ON alias.id = item_alias.alias_id
      INNER JOIN item_group_data igd ON id.item_group_data_id = igd.id
      INNER JOIN form_data fd ON igd.form_data_id = fd.id
      INNER JOIN study_event_data sed ON fd.study_event_data_id = sed.id
      INNER JOIN study_event se ON se.id = sed.study_event_id
      INNER JOIN subject ON sed.subject_id = subject.id
      INNER JOIN scheduled_activity sa ON fd.scheduled_activity_id = sa.id
      INNER JOIN cohort_assignment ca ON sed.cohort_assignment_id = ca.id
      INNER JOIN cohort ON ca.cohort_id = cohort.id
      INNER JOIN epoch ON epoch.id = cohort.epoch_id
      INNER JOIN study ON subject.study_id = study.id
      INNER JOIN form ON sa.form_id = form.id
      WHERE study.id = @study_id
      AND alias.name = 'Date of Visit' AND alias.context = 'Date of Visit' AND id.date_value IS NOT NULL
      GROUP BY subjectId) AS subjectLatestVisitDate ON subjectLatestVisitDate.subjectId = subject.id
   WHERE study.id= @study_id AND cohort_assignment.subject_activator_id IS NOT NULL