diff options
Diffstat (limited to 'queries')
| l--------- | queries | 1 | ||||
| -rw-r--r-- | queries/data_per_assay_per_year_per_outcome | 17 | ||||
| -rw-r--r-- | queries/donor_demo | 11 | ||||
| -rw-r--r-- | queries/donors_list | 1 | ||||
| -rw-r--r-- | queries/donors_outcome_across_years | 18 | ||||
| -rw-r--r-- | queries/high_reponders | 4 | ||||
| -rw-r--r-- | queries/mike_repeat_visit | 23 | ||||
| -rw-r--r-- | queries/null_donors | 7 | ||||
| -rw-r--r-- | queries/repeat_investigation | 5 | ||||
| -rw-r--r-- | queries/simon_data_extra | 22 | ||||
| -rw-r--r-- | queries/simon_initial | 16 | ||||
| -rw-r--r-- | queries/simon_initial_check | 16 | ||||
| -rw-r--r-- | queries/visit_multiple_21 | 11 | ||||
| -rw-r--r-- | queries/visits_all | 3 |
14 files changed, 154 insertions, 1 deletions
diff --git a/queries b/queries deleted file mode 120000 index 34efd61..0000000 --- a/queries +++ /dev/null @@ -1 +0,0 @@ -/home/mike/.local/share/db_ui/fluprint/
\ No newline at end of file diff --git a/queries/data_per_assay_per_year_per_outcome b/queries/data_per_assay_per_year_per_outcome new file mode 100644 index 0000000..2f8c063 --- /dev/null +++ b/queries/data_per_assay_per_year_per_outcome @@ -0,0 +1,17 @@ +SELECT donors.study_id AS study, + donors.id as donor, + donor_visits.visit_year AS year, + donor_visits.vaccine_resp AS outcome, + experimental_data.assay AS assay, + experimental_data.data AS data, + experimental_data.name_formatted as name, + experimental_data.units +FROM donors + LEFT JOIN donor_visits + ON donors.id = donor_visits.donor_id + INNER JOIN experimental_data + ON donor_visits.id = experimental_data.donor_visits_id + AND experimental_data.donor_id = donor_visits.donor_id +WHERE donors.gender IS NOT NULL + AND donor_visits.vaccine_resp IS NOT NULL +ORDER BY donor_visits.visit_year ASC diff --git a/queries/donor_demo b/queries/donor_demo new file mode 100644 index 0000000..dd5077a --- /dev/null +++ b/queries/donor_demo @@ -0,0 +1,11 @@ +SELECT donors.id as Donor, + donors.gender as Gender, + donor_visits.age_round as Age, + donors.race as Ethnicity, + donor_visits.cmv_status as 'CMV status', + donor_visits.vaccine_resp as Response +from donors + LEFT JOIN donor_visits + ON donors.id = donor_visits.donor_id + AND donor_visits.visit_id = 1 +where donor_visits.vaccine_resp is not null diff --git a/queries/donors_list b/queries/donors_list new file mode 100644 index 0000000..e3610bd --- /dev/null +++ b/queries/donors_list @@ -0,0 +1 @@ +SELECT * from `donors` diff --git a/queries/donors_outcome_across_years b/queries/donors_outcome_across_years new file mode 100644 index 0000000..1cbf55f --- /dev/null +++ b/queries/donors_outcome_across_years @@ -0,0 +1,18 @@ +SELECT donors.id AS donor_id, + donor_visits.age AS age, + donor_visits.vaccine_resp AS outcome, + donor_visits.visit_id AS visit, + donor_visits.visit_day AS day, + donor_visits.visit_year AS year + # experimental_data.name_formatted AS data_name, + # experimental_data.data AS data, +FROM donors + LEFT JOIN donor_visits + ON donors.id = donor_visits.donor_id + # AND donor_visits.visit_id = 1 + # INNER JOIN experimental_data + # ON donor_visits.id = experimental_data.donor_visits_id + # AND experimental_data.donor_id = donor_visits.donor_id +WHERE donors.gender IS NOT NULL + AND donor_visits.vaccine = 4 +ORDER BY donors.id DESC diff --git a/queries/high_reponders b/queries/high_reponders new file mode 100644 index 0000000..3f33373 --- /dev/null +++ b/queries/high_reponders @@ -0,0 +1,4 @@ +SELECT * from donor_visits +where vaccine_resp is not null +and donor_id = 166 +order by visit_year ASC diff --git a/queries/mike_repeat_visit b/queries/mike_repeat_visit new file mode 100644 index 0000000..c0bfbfd --- /dev/null +++ b/queries/mike_repeat_visit @@ -0,0 +1,23 @@ +SELECT donors.id AS donor_id, + donors.study_id AS study, + donor_visits.age AS age, + donor_visits.vaccine_resp AS outcome, + donor_visits.visit_year AS year, + donor_visits.visit_type_hai as type, + donor_visits.d_geo_mean as hai_response, + experimental_data.name AS name, + experimental_data.name_formatted AS name_formatted, + experimental_data.assay AS assay, + experimental_data.data AS data +FROM donors + LEFT JOIN donor_visits + ON donors.id = donor_visits.donor_id + INNER JOIN experimental_data + ON donor_visits.id = experimental_data.donor_visits_id + AND experimental_data.donor_id = donor_visits.donor_id +WHERE donors.gender IS NOT NULL + AND donor_visits.vaccine_resp IS NOT NULL + AND donor_visits.vaccine = 4 + AND donor_visits.visit_type_hai = "pre" + AND donor_visits.visit_id != 1 +ORDER BY donors.study_donor_id DESC diff --git a/queries/null_donors b/queries/null_donors new file mode 100644 index 0000000..4646677 --- /dev/null +++ b/queries/null_donors @@ -0,0 +1,7 @@ +SELECT * from donors +where donors.id is NULL or +donors.study_donor_id is NULL or +donors.study_id is NULL or +donors.study_internal_id is NULL or +donors.gender is NULL or +donors.race is NULL diff --git a/queries/repeat_investigation b/queries/repeat_investigation new file mode 100644 index 0000000..293e21c --- /dev/null +++ b/queries/repeat_investigation @@ -0,0 +1,5 @@ +SELECT * +from `donor_visits` +left join donors on + donors.id = donor_visits.donor_id +where study_id = 15 diff --git a/queries/simon_data_extra b/queries/simon_data_extra new file mode 100644 index 0000000..ea97abb --- /dev/null +++ b/queries/simon_data_extra @@ -0,0 +1,22 @@ +SELECT donors.id AS donor_id, + donors.study_id AS study, + donor_visits.age AS age, + donor_visits.vaccine_resp AS outcome, + donor_visits.visit_year AS year, + donor_visits.visit_type_hai as type, + donor_visits.d_geo_mean as hai_response, + experimental_data.name AS name, + experimental_data.name_formatted AS data_name, + experimental_data.assay AS assay, + experimental_data.data AS data +FROM donors + LEFT JOIN donor_visits + ON donors.id = donor_visits.donor_id + AND donor_visits.visit_id = 1 + INNER JOIN experimental_data + ON donor_visits.id = experimental_data.donor_visits_id + AND experimental_data.donor_id = donor_visits.donor_id +WHERE donors.gender IS NOT NULL + AND donor_visits.vaccine_resp IS NOT NULL + AND donor_visits.vaccine = 4 +ORDER BY donors.study_donor_id DESC diff --git a/queries/simon_initial b/queries/simon_initial new file mode 100644 index 0000000..f7ae18a --- /dev/null +++ b/queries/simon_initial @@ -0,0 +1,16 @@ +SELECT donors.id AS donor_id, + donor_visits.age AS age, + donor_visits.vaccine_resp AS outcome, + experimental_data.name_formatted AS data_name, + experimental_data.data AS data +FROM donors + LEFT JOIN donor_visits + ON donors.id = donor_visits.donor_id + AND donor_visits.visit_id = 1 + INNER JOIN experimental_data + ON donor_visits.id = experimental_data.donor_visits_id + AND experimental_data.donor_id = donor_visits.donor_id +WHERE donors.gender IS NOT NULL + AND donor_visits.vaccine_resp IS NOT NULL + AND donor_visits.vaccine = 4 +ORDER BY donors.study_donor_id DESC diff --git a/queries/simon_initial_check b/queries/simon_initial_check new file mode 100644 index 0000000..703a685 --- /dev/null +++ b/queries/simon_initial_check @@ -0,0 +1,16 @@ +SELECT donors.id AS donor_id, + donor_visits.age AS age, + donor_visits.vaccine_resp AS outcome, + # experimental_data.name_formatted AS data_name, + # experimental_data.data AS data, + donor_visits.visit_year AS year +FROM donors + LEFT JOIN donor_visits + ON donors.id = donor_visits.donor_id + # AND donor_visits.visit_id = 1 + # INNER JOIN experimental_data + # ON donor_visits.id = experimental_data.donor_visits_id + # AND experimental_data.donor_id = donor_visits.donor_id +WHERE donors.gender IS NOT NULL + AND donor_visits.vaccine = 4 +ORDER BY donors.id DESC diff --git a/queries/visit_multiple_21 b/queries/visit_multiple_21 new file mode 100644 index 0000000..d8a16fa --- /dev/null +++ b/queries/visit_multiple_21 @@ -0,0 +1,11 @@ +SELECT * from `donor_visits` +join( +select donor_visits.donor_id +from donor_visits +group by donor_visits.donor_id +having count(*) > 1) s on donor_visits.donor_id = s.donor_id +join donors +on donors.id = donor_visits.donor_id +where study_id = 21 +and donor_visits.donor_id = 166 +order by donor_visits.donor_id, visit_id diff --git a/queries/visits_all b/queries/visits_all new file mode 100644 index 0000000..0a6dcfa --- /dev/null +++ b/queries/visits_all @@ -0,0 +1,3 @@ +SELECT * from `donor_visits` +where donor_id = 779 +order by visit_year |