Changes between Version 10 and Version 11 of OnyxDatabaseReporting
- Timestamp:
- 01/14/13 16:07:31 (11 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
OnyxDatabaseReporting
v10 v11 89 89 == MySQL snippets == 90 90 91 Lists primary diagnoses with a count (used in the reporting table):91 # Lists primary diagnoses with a count (used in the reporting table): 92 92 93 93 SELECT category_name, count(category_name) … … 98 98 99 99 100 Lists BPt numbers, grouped and counted by primary diagnosis:100 # Lists BPt numbers, grouped and counted by primary diagnosis: 101 101 102 102 SELECT category_name, barcode, count(*) … … 108 108 GROUP BY category_name, barcode WITH ROLLUP; 109 109 110 From the command line, to query for gender and birth date for specific diagnosis, into an outfile:110 # From the command line, to query for gender and birth date for specific diagnosis, into an outfile: 111 111 112 112 mysql -h briccsdb -u auditor -p briccs -e " … … 122 122 123 123 124 Single diagnosis, count:124 # Single diagnosis, count: 125 125 126 126 SELECT count(participant.id) as 'Number of Participants' … … 133 133 134 134 135 Complex query, using two references to the same tables, to derive those of a certain diagnosis (primary or secondary) where the recruitment context is not 'Study specific':135 # Complex query, using two references to the same tables, to derive those of a certain diagnosis (primary or secondary) where the recruitment context is not 'Study specific': 136 136 137 137 SELECT count(participant.id) as 'AS not study recruited' … … 149 149 ; 150 150 151 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):151 # 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): 152 152 153 153 mysql -h briccsdb -u auditor -p briccs -e " … … 169 169 ; " > mysql-output.txt 170 170 171 Any one of a number of related primary or secondary diagnoses, aged under 65.171 # Any one of a number of related primary or secondary diagnoses, aged under 65. 172 172 173 173 SELECT count(distinct participant.id) as 'Number of Participants' … … 186 186 OR category_answer.category_name = 'Acute_Stent_Thrombosis' ) 187 187 ; 188 189 # To find incomplete interviews only for Study specific recruits 190 191 SELECT participant.barcode, participant.enrollment_id, participant.last_name, interview.start_date 192 FROM participant, interview, questionnaire_participant, question_answer, category_answer 193 WHERE 194 questionnaire_participant.participant_id = participant.id AND 195 question_answer.questionnaire_participant_id = questionnaire_participant.id AND 196 question_answer.question_name = "epi_type" AND 197 category_answer.question_answer_id = question_answer.id AND 198 category_answer.category_name = "Study_specific" AND 199 interview.status = "IN_PROGRESS" AND 200 interview.participant_id = participant.id 201 ORDER BY start_date 202 ;
