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

Step 5:

Query to get Laboratory data;

Documentation

Transfer Data Report Query

Analytics