= [[Aortic Stenosis Study]]

== SQL for identifying patients in Onyx database

# 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):

mysql -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


# To extract all relevant (study specific and by diagnosis) recruits with their recruitment dates, for compiling into a spreadsheet, use:

NORTHAMPTON

mysql -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

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, 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

LEICESTER

mysql -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

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, 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

Note these searches will export ethnicity but not exclude by it.

== Echo search for potential participants

=== Email From Nick

Hi Andy

 

I fed back to Muntaser today the useful discussion you and I had earlier in the week about the possibility of searching echo data in the CDW. He would very much like us to proceed. In the past, clinical colleagues have run ad hoc searches for his research project but reported that not all cases were being copied from Agfa Pacs into eCris. Is this still an issue, do you know?

 

What he would like us to do is a monthly routine search, including only ECHO reports from the previous complete month, and run the query around the middle of the following month so everyone will have had their results directly from their own clinician before the research team contact them. The search terms should include “aortic stenosis” but as we discussed looking to exclude any patients where the phrase “no X aortic stenosis” where X is any amount of additional language but only within a single sentence. Initially, we would like this ‘exclusion’ criteria to not actually exclude the patient but to result in a flag showing up in the results, so we can evaluate whether the exclusion criteria would be helpful or result in lots of false negatives.

The result set should include the patient’s age, ethnicity, S number and the result text itself please.

Does this sound feasible? How quickly could we get a sample implementation in place?

Thanks

Nick

== Action: Andy Carruthers

Andy is to create a scheduled search to which will return results and pass those values through DAPS.  The echo text will be returned along with the DAPS data with an indicator to say whether the search algorithm thinks the search text has been negated.  The search view will be amendable by the BRU users.

[[BackLinks]]