Changes between Initial Version and Version 1 of OnyxDatabaseReporting


Ignore:
Timestamp:
06/20/11 11:57:04 (13 years ago)
Author:
Nick Holden
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OnyxDatabaseReporting

    v1 v1  
     1MySQL snippets and other material relating to reporting from the Onyx database.
     2
     3In the early phase of BRICCS, before i2b2 is deployed, obtaining data on BRICCS participants is possible only from within the Onyx database, via MySQL queries.
     4
     5There is a web page for reporting from the Onyx database, accessible via password within the UHL network, contact Nick or Vasil for information. In addition, the following is provided as useful material to assist when writing custom MySQL queries for reporting:
     6
     7
     8== Table Structure ==
     9
     10mysql> describe participant;
     11+------------------+--------------+------+-----+---------+----------------+
     12| Field            | Type         | Null | Key | Default | Extra          |
     13+------------------+--------------+------+-----+---------+----------------+
     14| id               | bigint(20)   | NO   | PRI | NULL    | auto_increment |
     15| barcode          | varchar(250) | YES  |     | NULL    |                |
     16| birth_date       | date         | YES  |     | NULL    |                |
     17| enrollment_id    | varchar(255) | YES  |     | NULL    |                |
     18| first_name       | varchar(250) | NO   |     | NULL    |                |
     19| gender           | varchar(255) | YES  |     | NULL    |                |
     20| last_name        | varchar(250) | NO   |     | NULL    |                |
     21| recruitment_type | varchar(255) | YES  |     | NULL    |                |
     22| site_no          | varchar(255) | NO   |     | NULL    |                |
     23+------------------+--------------+------+-----+---------+----------------+
     249 rows in set (0.00 sec)
     25
     26mysql> describe question_answer;
     27+------------------------------+---------------+------+-----+---------+----------------+
     28| Field                        | Type          | Null | Key | Default | Extra          |
     29+------------------------------+---------------+------+-----+---------+----------------+
     30| id                           | bigint(20)    | NO   | PRI | NULL    | auto_increment |
     31| active                       | bit(1)        | NO   |     | NULL    |                |
     32| comment                      | varchar(2000) | YES  |     | NULL    |                |
     33| question_name                | varchar(255)  | NO   | MUL | NULL    |                |
     34| questionnaire_participant_id | bigint(20)    | NO   | MUL | NULL    |                |
     35+------------------------------+---------------+------+-----+---------+----------------+
     365 rows in set (0.00 sec)
     37
     38mysql> describe questionnaire_participant;
     39+-----------------------+--------------+------+-----+---------+----------------+
     40| Field                 | Type         | Null | Key | Default | Extra          |
     41+-----------------------+--------------+------+-----+---------+----------------+
     42| id                    | bigint(20)   | NO   | PRI | NULL    | auto_increment |
     43| locale                | varchar(255) | NO   |     | NULL    |                |
     44| questionnaire_name    | varchar(255) | NO   |     | NULL    |                |
     45| questionnaire_version | varchar(255) | NO   |     | NULL    |                |
     46| resume_page           | varchar(255) | YES  |     | NULL    |                |
     47| time_end              | datetime     | YES  |     | NULL    |                |
     48| time_start            | datetime     | NO   |     | NULL    |                |
     49| participant_id        | bigint(20)   | NO   | MUL | NULL    |                |
     50| user_id               | bigint(20)   | NO   | MUL | NULL    |                |
     51+-----------------------+--------------+------+-----+---------+----------------+
     529 rows in set (0.00 sec)
     53
     54== MySQL snippets ==
     55
     56Lists primary diagnoses with a count (used in the reporting table):
     57
     58SELECT category_name, count(category_name)
     59          FROM question_answer, category_answer
     60          where question_answer.question_name = 'epi_pridiag'
     61         and category_answer.question_answer_id = question_answer.id
     62         group by category_name order by count(category_name) desc
     63
     64
     65Lists BPt numbers, grouped and counted by primary diagnosis:
     66
     67SELECT category_name, barcode, count(*)
     68        FROM participant, questionnaire_participant, question_answer, category_answer
     69        WHERE participant.id = questionnaire_participant.participant_id
     70        AND question_answer.questionnaire_participant_id = questionnaire_participant.id
     71        AND question_answer.question_name = 'epi_pridiag'
     72        AND category_answer.question_answer_id = question_answer.id
     73        GROUP BY category_name, barcode WITH ROLLUP;
     74