Transfer Data Report Query

This page lists all queries used for generating Transfer data report.

Step 1:

Get list of subject

SELECT subject.id, subject.randomization_number, subject.screening_number, subject.lead_in_number,
    study_site.id AS studySiteId, study_site.location_oid, study_site.central_laboratory_id, site.time_zone_id, study.name AS studyName,
    study.protocol_name,
    volunteer.gender_male, volunteer.date_of_birth
  FROM subject
    INNER JOIN study_site ON study_site.id = subject.study_site_id
    INNER JOIN volunteer ON subject.volunteer_id = volunteer.id
    INNER JOIN study ON study.id = study_site.study_id
    INNER JOIN site ON site.id = study_site.site_id
  WHERE subject.study_id = <study Id> AND subject_study_status IN ('Active', 'Complete', 'Withdrawn', 'Terminated')
  AND (subject.randomization_number IS NOT NULL AND subject.randomization_number <> '')

Step 2:

Get meta data for the report

-- Get study event names (Visit names)
SELECT study_event.name FROM study_event
    INNER JOIN study ON study.study_meta_data_id = study_event.study_meta_data_id
  WHERE study.id = <study id>
  
-- Get Epoch and cohort names  
SELECT epoch.name AS epochName, cohort.name AS cohortName FROM cohort
    INNER JOIN epoch ON epoch.id = cohort.epoch_id
  WHERE epoch.study_id = <study id>
    
 -- Get study information    
 SELECT study.name, study.protocol_name, org.time_zone_id AS orgTz, study_site.location_oid, site.time_zone_id AS siteTz FROM study
      INNER JOIN org ON org.id = study.org_id
      LEFT JOIN study_site ON study_site.study_id = study.id
      INNER JOIN site ON site.id = study_site.site_id
    WHERE study.id <study id>
  
 -- Get measurements   
 SELECT measurement_unit.symbol FROM measurement_unit
        INNER JOIN study ON study.study_meta_data_id = measurement_unit.study_meta_data_id
      WHERE study.id = <study id>

Step 3:

Get list of Item Groups and Items

-- Get list of item group ref ids
SELECT igr.id
  FROM item_group_ref igr
    INNER JOIN form f ON igr.form_id = f.id
    INNER JOIN study s ON f.study_meta_data_id = s.study_meta_data_id
  WHERE s.id=<study id>
  
  -- Get list of items
  SELECT
    item.id AS itemId,
    item.study_lab_panel_id AS studyLabPanelId,
    item.data_type,
    item.length,
    item.name,
    CASE
      WHEN (cdash_alias.name is not null) then cdash_alias.name
      ELSE item.sas_field_name
    END AS cdashOrSasFieldName,
    item.sds_var_name,
    item_ref.order_number,
    item_ref.id AS itemRefId,
    item.item_data_origin,
    item.significant_digits,
    item.base_study_item_type,
    item.label_field_one,
    item.label_field_two,
    item.loinc_code AS loinc_code,
    item.loinc_code" : "null"} AS loinc_code,
    item.description,
    item_group_ref.id AS itemGroupRefId,
    item_group.name   AS igName,
    item_group.description AS itemGroupDescription,
    item_group.comment,
    item_group.domain,
    item_group.repeating,
    item_group.sas_dataset_name,
    form.name                 AS formName,
    edc_device_parameter.name AS edcDeviceParamName,
    code_list.id              AS codeListId,
    code_list.sas_format_name AS codeListSasFormatName,
    term_configuration.med_dra_dictionary_id,
    term_configuration.who_drug_dictionary_id
FROM
    item_ref
LEFT JOIN
    edc_device_parameter
ON
    edc_device_parameter.id = item_ref.edc_device_parameter_id
INNER JOIN
    item
ON
    item.id = item_ref.item_id
LEFT JOIN
    code_list
ON
    code_list.id = item.code_list_id
INNER JOIN
    item_group
ON
    item_group.id = item_ref.item_group_id
INNER JOIN
    item_group_ref
ON
    item_group.id = item_group_ref.item_group_id
INNER JOIN
    form
ON
    form.id = item_group_ref.form_id
LEFT OUTER JOIN
    term_configuration
ON
    item_ref.id = term_configuration.item_ref_id
    LEFT JOIN
        ( SELECT ia.item_aliases_id, a.name
              from item_alias ia INNER JOIN alias a ON ia.alias_id = a.id
              WHERE a.context='CDASH' ) AS cdash_alias
    ON
    item.id = cdash_alias.item_aliases_id
WHERE
    item_group_ref.id IN (<List of item group ref from 1st query>

Step 4:

Get Item data for each domains

SELECT study_event_data.subject_id AS subjectId, item.sas_field_name, item.sds_var_name,item_data.value, item_data.date_value, item_data.item_ref_id AS itemRefId, item_ref.blinded,
          item_data.id AS itemDataId, code_list_item.coded_value,
          interval_container_summary.interval_container_type, interval_container_summary.net_weight, interval_container_summary.ph, interval_container_summary.specific_gravity,
          form_data.id AS formDataId, form_data.timepoint, item_group_data.collection_time, item_group_data.id AS igdId, item_group_data.item_group_repeat_key,
          item_group_data.item_group_ref_id, epoch.name AS epochName, cohort.name AS cohortName, study_event.visit_num,
          study_event.name AS studyEventName, measurement_unit.symbol, subject.screening_number,
          coded_item_data.id AS coded_item_data_id, coded_item_data.*, term_configuration.med_dra_dictionary_id, med_dra_dictionary.dictionary_version,
          term_configuration.who_drug_dictionary_id, who_drug_dictionary.version_short_description, segment.name AS segmentName
    FROM item_data
      INNER JOIN item_ref ON item_ref.id = item_data.item_ref_id
      INNER JOIN item ON item.id = item_ref.item_id
      LEFT JOIN code_list_item ON code_list_item.id = item_data.code_list_item_id
      LEFT JOIN interval_container_summary ON interval_container_summary.id = item_data.interval_container_summary_id
      LEFT JOIN measurement_unit ON measurement_unit.id = item_data.measurement_unit_id
      INNER JOIN item_group_data ON item_group_data.id = item_data.item_group_data_id
      INNER JOIN form_data ON form_data.id = item_group_data.form_data_id
      LEFT JOIN scheduled_activity ON scheduled_activity.id = form_data.scheduled_activity_id
      INNER JOIN study_event_data ON study_event_data.id = form_data.study_event_data_id
      LEFT JOIN subject ON subject.id = study_event_data.subject_id
      LEFT JOIN cohort_assignment ON cohort_assignment.id = study_event_data.cohort_assignment_id
      LEFT JOIN cohort ON cohort.id = cohort_assignment.cohort_id
      LEFT JOIN epoch ON epoch.id = cohort.epoch_id
      INNER JOIN study_event ON study_event.id = study_event_data.study_event_id
      LEFT OUTER JOIN coded_item_data ON coded_item_data.item_data_id = item_data.id
      LEFT OUTER JOIN term_configuration ON term_configuration.item_ref_id = item_data.item_ref_id
      LEFT OUTER JOIN med_dra_dictionary ON med_dra_dictionary.id = term_configuration.med_dra_dictionary_id
      LEFT OUTER JOIN who_drug_dictionary ON who_drug_dictionary.id = term_configuration.who_drug_dictionary_id
      LEFT OUTER JOIN segment ON segment.id = scheduled_activity.segment_id
      WHERE study_event_data.subject_id IN (<subject ids>)
      AND item_data.canceled <> true AND item_group_data.canceled <> true
          AND form_data.canceled <> true AND study_event_data.canceled <> true AND item_data.data_collection_status = 'Complete'
          AND item_group_data.data_collection_status <> 'Invisible' AND form_data.data_collection_status <> 'Invisible' 
          AND item_group_data.item_group_ref_id in (<item group ref ids for each domain>)

Step 5:

Query to get Laboratory data;

SELECT
    study_event_data.subject_id AS subjectId,
    item_data.id AS itemDataId,
    item_data.date_value AS itemDataUpdatedDate,
    form_data.id AS formDataId,
    form_data.unscheduled_form_id,
    epoch.name  AS epochName,
    cohort.name AS cohortName,
    study_event.visit_num,
    item_group_data.collection_time,
    item_group_data.id AS itemGroupDataId,
    item_group_ref.id AS itemGroupRefId,
    form_data.timepoint,
    base_specimen.id AS baseSpecimenId,
    base_specimen.specimen_material_id,
    base_specimen.lab_specimen_comments,
    base_specimen.accession_id,
    base_battery.id AS baseBatteryId,
    base_battery.battery_name,
    base_battery.battery_id,
    study_event.name AS studyEventName,
    item.id AS itemId,
    item.name AS itemName,
    study_lab_panel.name AS studyLabPanelName,
    study_lab_panel.specimen_category AS studyLabPanelSpecimenCategory,
    base_test_result.id  AS baseTestResultId,
    base_test_result.application_user_role_id as applicationUserRoleId,
    base_test_result.lab_test_name,
    base_test_result.lab_test_id,
    base_test_result.limits,
    base_test_result.loinc_code,
    base_test_result.report_result_type,
    base_test_result.reported_text_result,
    base_test_result.reported_numeric_result,
    base_test_result.alert_flag,
    base_test_result.reported_units,
    base_test_result.test_status,
    base_test_result_review.date_created AS baseTestResultReviewDate,
    base_test_result_review.clinical_significance,
    base_test_result_review.repeat_lab,
    base_test_result.reported_reference_range_low,
    base_test_result.reported_reference_range_high,
    base_test_result.delta_flag,
    base_test_result.test_level_comments,
    base_test_result.reported_date_and_time,
    form.name AS formName,
    segment.name AS segmentName,
    unsForm.name AS unsFormName,
    item_data.item_ref_id
FROM
    base_test_result 
LEFT JOIN
    base_test_result_review
ON
    base_test_result.base_test_result_review_id = base_test_result_review.id
INNER JOIN
    base_battery
ON
    base_battery.id = base_test_result.base_battery_id
INNER JOIN
    base_specimen
ON
    base_specimen.id = base_battery.base_specimen_id
    INNER JOIN
    item_data
ON
    item_data.base_specimen_id = base_specimen.id
INNER JOIN
    item_ref
ON
    item_ref.id = item_data.item_ref_id
INNER JOIN
    item
ON
    item.id = item_ref.item_id
INNER JOIN
    study_lab_panel
ON
    study_lab_panel.id = item.study_lab_panel_id
INNER JOIN
    item_group_data
ON
    item_group_data.id = item_data.item_group_data_id
INNER JOIN
    form_data
ON
    form_data.id = item_group_data.form_data_id
INNER JOIN
    study_event_data
ON
    study_event_data.id = form_data.study_event_data_id
LEFT JOIN
    cohort_assignment
ON
    cohort_assignment.id = study_event_data.cohort_assignment_id
LEFT JOIN
    cohort
ON
    cohort.id = cohort_assignment.cohort_id
LEFT JOIN
    epoch
ON
    epoch.id = cohort.epoch_id
INNER JOIN
    study_event
ON
    study_event.id = study_event_data.study_event_id
INNER JOIN
    item_group_ref
ON
    item_group_ref.id = item_group_data.item_group_ref_id
INNER JOIN
    item_group
ON
    item_group.id = item_group_ref.item_group_id
LEFT JOIN
    scheduled_activity
ON
    scheduled_activity.id = form_data.scheduled_activity_id
LEFT JOIN
    form
ON
    form.id = scheduled_activity.form_id    
LEFT JOIN
    form AS unsForm
ON
    unsForm.id = form_data.unscheduled_form_id
LEFT JOIN 
    segment
ON
    segment.id = scheduled_activity.segment_id    
WHERE
    study_event_data.subject_id IN (<subject ids>)
AND base_test_result.archived <> TRUE
AND base_battery.archived <> TRUE
AND item_data.canceled <> TRUE
AND item_group_data.canceled <> TRUE
AND form_data.canceled <> TRUE