Changes between Version 1 and Version 2 of Legacy - Aortic Stenosis Study


Ignore:
Timestamp:
03/05/14 15:34:57 (12 years ago)
Author:
Nick Holden
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Legacy - Aortic Stenosis Study

    v1 v2  
    11= BRICCS Aortic Stenosis Study
    22
    3 == Echo Search
     3== SQL for identifying patients in Onyx database
     4
     5# Using UNION to produce a list of S numbers and DOBs for participants who are either study specific recruits or have a specific primary or secondary diagnosis (UNION combines two SELECTs with duplicates removed):
     6
     7mysql -h briccsdb -u auditor -p briccs -e " SELECT enrollment_id as 'Participant', birth_date as 'DOB' FROM participant, questionnaire_participant, question_answer, category_answer WHERE participant.id = questionnaire_participant.participant_id AND question_answer.questionnaire_participant_id = questionnaire_participant.id AND ( question_answer.question_name = 'epi_pridiag' OR question_answer.question_name = 'epi_secdiag' ) AND category_answer.question_answer_id = question_answer.id AND category_answer.category_name = 'Aortic_Stenosis' UNION SELECT enrollment_id as 'Participant', birth_date as 'DOB' FROM participant, questionnaire_participant, question_answer, category_answer WHERE participant.id = questionnaire_participant.participant_id AND question_answer.questionnaire_participant_id = questionnaire_participant.id AND question_answer.question_name = 'epi_type' AND category_answer.question_answer_id = question_answer.id AND category_answer.category_name = 'Study_specific' ; " > mysql-output.txt
     8
     9
     10# To extract all relevant (study specific and by diagnosis) recruits with their recruitment dates, for compiling into a spreadsheet, use:
     11
     12NORTHAMPTON
     13
     14mysql -h briccsdb -u auditor -p briccs_northampton -e " SELECT barcode as 'Participant', enrollment_id as 'S number', gender as 'Gender', birth_date as 'DOB', start_date as 'Questionnaire Date', participant_attribute_value.text_value as 'Ethnic Origin' FROM participant, questionnaire_participant, interview, question_answer, category_answer, participant_attribute_value WHERE participant.id = questionnaire_participant.participant_id AND participant.id = interview.participant_id AND question_answer.questionnaire_participant_id = questionnaire_participant.id AND ( question_answer.question_name = 'epi_pridiag' OR question_answer.question_name = 'epi_secdiag' ) AND category_answer.question_answer_id = question_answer.id AND category_answer.category_name = 'Aortic_Stenosis' AND participant_attribute_value.participant_id = participant.id AND participant_attribute_value.attribute_name = 'pat_ethnicity' UNION
     15
     16SELECT barcode as 'Participant', enrollment_id as 'S number', gender as 'Gender', birth_date as 'DOB',start_date as 'Questionnaire Date', participant_attribute_value.text_value as 'Ethnic Origin' FROM participant, interview, questionnaire_participant, question_answer, category_answer, participant_attribute_value WHERE participant.id = questionnaire_participant.participant_id AND participant.id = interview.participant_id AND question_answer.questionnaire_participant_id = questionnaire_participant.id AND question_answer.question_name = 'epi_type' AND category_answer.question_answer_id = question_answer.id AND category_answer.category_name = 'Study_specific' AND participant_attribute_value.participant_id = participant.id AND participant_attribute_value.attribute_name = 'pat_ethnicity'; " > northampton-output.txt
     17
     18LEICESTER
     19
     20mysql -h briccsdb -u auditor -p briccs -e " SELECT barcode as 'Participant', enrollment_id as 'S number', gender as 'Gender', birth_date as 'DOB', start_date as 'Questionnaire Date', participant_attribute_value.text_value as 'Ethnic Origin' FROM participant, questionnaire_participant, interview, question_answer, category_answer, participant_attribute_value WHERE participant.id = questionnaire_participant.participant_id AND participant.id = interview.participant_id AND question_answer.questionnaire_participant_id = questionnaire_participant.id AND ( question_answer.question_name = 'epi_pridiag' OR question_answer.question_name = 'epi_secdiag' ) AND category_answer.question_answer_id = question_answer.id AND category_answer.category_name = 'Aortic_Stenosis' AND participant_attribute_value.participant_id = participant.id AND participant_attribute_value.attribute_name = 'pat_ethnicity' UNION
     21
     22SELECT barcode as 'Participant', enrollment_id as 'S number', gender as 'Gender', birth_date as 'DOB',start_date as 'Questionnaire Date', participant_attribute_value.text_value as 'Ethnic Origin' FROM participant, interview, questionnaire_participant, question_answer, category_answer, participant_attribute_value WHERE participant.id = questionnaire_participant.participant_id AND participant.id = interview.participant_id AND question_answer.questionnaire_participant_id = questionnaire_participant.id AND question_answer.question_name = 'epi_type' AND category_answer.question_answer_id = question_answer.id AND category_answer.category_name = 'Study_specific' AND participant_attribute_value.participant_id = participant.id AND participant_attribute_value.attribute_name = 'pat_ethnicity'; " > leicester-output.txt
     23
     24Note these searches will export ethnicity but not exclude by it.
     25
     26== Echo search for potential participants
    427
    528=== Email From Nick