Sample queries

From CTRNet Wiki
Jump to: navigation, search

The SQL code for these queries is available for viewing on this page. The code is also available for download.

This page will be used to share common reports and queries developed by ATiM users. Collection Query


Basic Collections query -searches by label, bank, site, and date.

Tables:

  • Collections

Criteria:

  • Acquisition Label
  • Bank
  • Collection Site
  • Collection Date(Range)

Result Set:

  • Acquisition Label
  • Bank
  • Collection Site
  • Collection Date

SQL: 

SELECT 
   Collection.id, 
   Collection.acquisition_label, 
   Collection.bank, 
   Collection.collection_site, 
   Collection.collection_datetime
FROM 
   `collections` AS Collection 
WHERE 
   Collection.acquisition_label = "@@Collection.acquisition_label@@" AND 
   Collection.bank = "@@Collection.bank@@" AND 
   Collection.collection_site = "@@Collection.collection_site@@" AND 
   Collection.collection_datetime >= "@@Collection.collection_datetime_start@@" AND 
   Collection.collection_datetime <= "@@Collection.collection_datetime_end@@";


Basic Consents query - searches by version, status, date, operation date, and surgeon.

Tables:

  • Consents

Criteria:

  • Form Version
  • Consent Status
  • Status Date(Range)
  • Operation Date(Range)
  • Surgeon

Result Set:

  • Form Version
  • Consent Status
  • Status Date
  • Operation Date
  • Surgeon

SQL: 

SELECT
   Consent.id,
   Consent.form_version,
   Consent.consent_status,
   Consent.status_date,
   Consent.surgeon,
   Consent.operation_date
FROM
   `consents` AS Consent
WHERE
   Consent.form_version = "@@Consent.form_version@@"
   AND Consent.consent_status = "@@Consent.consent_status@@"
   AND Consent.status_date >= "@@Consent.status_date_start@@"
   AND Consent.status_date <= "@@Consent.status_date_end@@"
   AND Consent.surgeon = "@@Consent.surgeon@@"
   AND Consent.operation_date >= "@@Consent.operation_date_start@@"
   AND Consent.operation_date <= "@@Consent.operation_date_end@@";

Participant follow-up query -searches by a participant's tumour bank number, first name, and last name, and returns. It returns a participant's vital status, and when their follow-up occured.

Tables:

  • Participants
  • Event Masters
  • Event Detail Follow-up(ed_all_clinical_followup)

Criteria:

  • Tumour Bank Number
  • First Name
  • Last Name

Result Set:

  • Tumour Bank Number
  • First Name
  • Last Name
  • Vital Status
  • Event Date

SQL: 

SELECT
   Participant.id,
   Participant.tb_number,
   Participant.first_name,
   Participant.last_name,
   EventDetail.id,
   EventDetail.vital_status,
   EventMaster.id,
   EventMaster.event_date
FROM
   `participants` AS Participant,
   `event_masters` AS EventMaster,
   `ed_all_clinical_followup` AS EventDetail
WHERE
   EventMaster.participant_id = Participant.id
   AND EventDetail.event_master_id = EventMaster.id
   AND Participant.tb_number = "@@Participant.tb_number@@"
   AND Participant.first_name = "@@Participant.first_name@@"
   AND Participant.last_name = "@@Participant.last_name@@";

Diagnosis query -searches by a participant's tumour bank number, age at diagnosis, first name, and last name, and by diagnoses' number, nature, origin, date, icd-10, and morphology.

Tables:

  • Participants
  • Diagnoses

Criteria:

  • Tumour Bank Number
  • First Name
  • Last Name
  • Diagnosis Number
  • Diagnosis Origin
  • Diagnosis Nature
  • Diagnosis Date
  • ICD-10 Identification Number
  • Morphology
  • Age at Diagnosis

Result Set:

  • Tumour Bank Number
  • First Name
  • Last Name
  • Diagnosis Number
  • Diagnosis Origin
  • Diagnosis Nature
  • Diagnosis Date
  • ICD-10 Identification Number
  • Morphology
  • Age at Diagnosis

SQL: 

SELECT
   Participant.id,
   Participant.tb_number,
   Participant.first_name,
   Participant.last_name,
   Diagnosis.id,
   Diagnosis.dx_number,
   Diagnosis.dx_nature,
   Diagnosis.dx_origin,
   Diagnosis.dx_date,
   Diagnosis.icd10_id,
   Diagnosis.morphology,
   Diagnosis.age_at_dx
FROM
   `diagnoses` AS Diagnosis
   `participants` AS Participant
WHERE
   Diagnosis.dx_number = "@@Diagnosis.dx_number@@"
   AND Diagnosis.dx_nature = "@@Diagnosis.dx_nature@@"
   AND Diagnosis.icd10_id = "@@Diagnosis.icd10_id@@"
   AND Diagnosis.morphology = "@@Diagnosis.morphology@@"
   AND Diagnosis.dx_origin = "@@Diagnosis.dx_origin@@"
   AND Participant.tb_number = "@@Participant.tb_number@@"
   AND Participant.first_name = "@@Participant.first_name@@"
   AND Participant.last_name = "@@Participant.last_name@@"
   AND Diagnosis.participant_id = Participant.id;

Participant count query -returns the number of participants based on search criteria. The query searches by creation date, sex, date of birth, race, and vital status.

Tables:

  • Participants

Criteria:

  • Created(Range)
  • Sex
  • Date of Birth
  • Race
  • Vital Status

Result Set:

  • Number of Participants

SQL: 

SELECT
   COUNT(*) AS participant_count
FROM
   `participants` AS Participant
WHERE
   Participant.sex = "@@Participant.sex@@"
   AND Participant.created >= "@@Participant.created_start@@"
   AND Participant.created <= "@@Participant.created_end@@"
   AND Participant.date_of_birth >= "@@Participant.date_of_birth_start@@"
   AND Participant.date_of_birth <= "@@Participant.date_of_birth_end@@"
   AND Participant.race = "@@Participant.race@@"
   AND Participant.vital_status = "@@Participant.vital_status@@";

Annotation event query
-searches by a participant's tumour bank number, first name, last name, disease site, and event type.

Tables:

  • Participants
  • Event Masters

Criteria:

  • Tumour Bank Number
  • First Name
  • Last Name
  • Disease Site
  • Event Type

Result Set:

  • Tumour Bank Number
  • First Name
  • Last Name
  • Disease Site
  • Event Type

SQL: 

SELECT
   Participant.id,
   Participant.tb_number,
   Participant.first_name,
   Participant.last_name,
   EventMaster.id,
   EventMaster.event_type,
   EventMaster.disease_site
FROM
   `participants` AS Participant,
   `event_masters` AS EventMaster
WHERE
   Participant.tb_number = "@@Participant.tb_number@@"
   AND Participant.first_name = "@@Participant.first_name@@"
   AND Participant.last_name = "@@Participant.last_name@@"
   AND EventMaster.disease_site = "@@EventMaster.disease_site@@"
   AND EventMaster.event_type = "@@EventMaster.event_type@@"
   AND EventMaster.participant_id = Participant.id;


Accrual rates query
-searches by the bank where a collection took place as well as when a collection took place. It will return the criteria along with the acquisition label, all the sample's codes and types.

Tables:

  • Collection
  • Sample Master

Criteria:

  • Bank
  • Collection date

Result Set:

  • Acquisition Label
  • Bank
  • Collection Date
  • Sample Code
  • Sample Type

SQL: 

SELECT
   Collection.id,
   Collection.acquisition_label,
   Collection.bank,
   Collection.collection_datetime,
   SampleMaster.id,
   SampleMaster.sample_code,
   SampleMaster.sample_type
FROM
   `collections` AS Collection,
   `sample_masters` AS SampleMaster
WHERE
   Collection.bank = "@@Collection.bank@@"
   AND Collection.collection_datetime >= "@@Collection.collection_datetime_start@@"
   AND Collection.collection_datetime <="@@Collection.collection_datetime_end@@"
   AND SampleMaster.collection_id = Collection.id;


Participant creation query
Queries all participants created during a certain date range.

Tables:

  • Participants

Criteria:

  • Created(Range)

Result Set:

  • First Name
  • Last Name
  • Tumour Bank Number
  • Created
  • Consent status
  • Consent date

SQL: 

SELECT
   Participant.id,
   Participant.tb_number,
   Participant.first_name,
   Participant.last_name,
   Participant.created,
   Consent.id,
   Consent.consent_status,
   Consent.status_date
FROM
   `participants` AS Participant,
   `consents` AS Consent
WHERE
   Participant.created >= "@@Participant.created_start@@"
   AND Participant.created <= "@@Participant.created_end@@";

Blood accrual rate query
-searches by type, collection date, and collection bank.

Tables:

  • Collections
  • Sample Masters
  • Sample Details Blood(sd_spe_bloods)

Criteria:

  • Bank
  • Blood Type
  • Collection Date

Result Set:

  • Acquisition Label
  • Bank
  • Collection Date
  • Sample Code
  • Blood Type

SQL: 

SELECT
   Collection.id,
   Collection.acquisition_label,
   Collection.bank,
   Collection.collection_datetime,
   SampleMaster.id,
   SampleMaster.sample_code,
   SampleDetail.id,
   SampleDetail.type
FROM
   `collections` AS Collection,
   `sample_masters` AS SampleMaster,
   `sd_spe_bloods` AS SampleDetail
WHERE
   Collection.bank = "@@Collection.bank@@"
   AND Collection.collection_datetime >= "@@Collection.collection_datetime_start@@"
   AND Collection.collection_datetime <="@@Collection.collection_datetime_end@@"
   AND SampleDetail.type = "@@SampleDetail.type@@"
   AND SampleMaster.collection_id = Collection.id
   AND SampleDetail.sample_master_id = SampleMaster.id;

Tissue accrual rate query
-searches by tissue type, collection date, and collection bank.

Tables:

  • Collections
  • Sample Masters
  • Sample Details Tissue(sd_spe_tissues)

Criteria:

  • Bank
  • Tissue Source
  • Collection Date

Result Set:

  • Acquisition Label
  • Bank
  • Collection Date
  • Sample Code
  • Tissue Source

SQL: 

SELECT
   Collection.id,
   Collection.acquisition_label,
   Collection.bank,
   Collection.collection_datetime,
   SampleMaster.id,
   SampleMaster.sample_code,
   SampleDetail.id,
   SampleDetail.tissue_source
FROM
   `collections` AS Collection,
   `sample_masters` AS SampleMaster,
   `sd_spe_tissues` AS SampleDetail
WHERE
   Collection.bank = "@@Collection.bank@@"
   AND Collection.collection_datetime >= "@@Collection.collection_datetime_start@@"
   AND Collection. collection_datetime <="@@Collection.collection_datetime_end@@"
   AND SampleDetail.tissue_source = "@@SampleDetail.tissue_source@@"
   AND SampleMaster.collection_id = Collection.id
   AND SampleDetail.sample_master_id = SampleMaster.id;

Sample Accrual Rates by Bank and Type
-searches by collection date.
Note: If querying straight from the database, the collection_datetime will be inaccurate. It is just for use with the query tool.

Tables:

  • Collections
  • Banks
  • Sample Masters

Criteria:

  • Collection Date

Result Set:

  • Number of Samples by Type(x-axis) and by Bank(y-axis)

SQL: 

SELECT
  SampleMaster.`sample_type` AS "Sample Type",
  SUM(IF(Bank.`id`=1,1,0)) AS "TestBank1",
  SUM(IF(Bank.`id`=2,1,0)) AS "TestBank2",
  SUM(IF(Bank.`id`=3,1,0)) AS "TestBank3"
  Collection.`collection_datetime`
FROM
  `sample_masters` AS SampleMaster,
  `banks` AS Bank,
  `collections` AS Collection
WHERE
  SampleMaster.`collection_id` = c.`id`
  AND Collection.`bank` LIKE b.`name`
  AND Collection.`collection_datetime` >= "@@Collection.collection_datetime_start@@"
  AND Collection.`collection_datetime` <="@@Collection.collection_datetime_end@@"
GROUP BY SampleMaster.`sample_type`;
Retrieved from "http://www.ctrnet.ca/mediawiki/index.php/Sample_queries"
Personal tools