Version 2 (modified by 11 years ago) ( diff ) | ,
---|
CiviCRM Graphic 2 Patient Export
Direct Query
Requirements
- Bring across consents and IDs etc from the civicrm_value_graphic2_10 table.
- Check that the patient has one of the required statuses in the civicrm_case.status_id column. These are: 6: Available for cohort, 10: Completed, 1: Open, 5: Recruited.
Query
As usual the queries are different for test and for live. Hurray!
Test
SELECT recruit.id civiCRM_contact_id , cids.nhs_number_1 nhs_number , cids.uhl_s_number_2 uhl_s_number , grd.genvasc_id_10 genvasc_id , grd.genvasc_consent_q1_11 consent_1 , grd.genvasc_consent_q2_12 consent_2 , grd.genvasc_consent_q3_13 consent_3 , grd.genvasc_consent_q4_14 consent_4 , grd.genvasc_consent_q5_15 consent_5 , grd.genvasc_consent_q6_16 consent_6 , grd.genvasc_consent_q7_17 consent_7 , recruit.birth_date , enrollment.start_date enrollment_date , CASE WHEN (wstatus.withdrawal_status_24 = 'A') THEN enrollment.end_date END withdrawal_date FROM civicrm_contact recruit JOIN civicrm_case_contact enrollcontact ON enrollcontact.contact_id = recruit.id JOIN civicrm_case enrollment ON enrollment.id = enrollcontact.case_id AND enrollment.start_date < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND enrollment.status_id NOT IN (699 /* declined */, 701 /* excluded */) AND enrollment.case_type_id = "3" /* GENVASC */ AND enrollment.is_deleted = 0 JOIN civicrm_value_contact_ids_2 cids ON cids.entity_id = recruit.id JOIN civicrm_value_genvasc_recruitment_data_6 grd ON grd.entity_id = enrollment.id LEFT JOIN civicrm_value_genvasc_withdrawal_status_9 wstatus ON wstatus.entity_id = enrollment.id WHERE recruit.is_deleted = 0 AND COALESCE(wstatus.withdrawal_status_24, '') <> 'B' AND EXISTS ( SELECT 1 FROM civicrm_relationship rel WHERE rel.contact_id_a = recruit.id AND rel.relationship_type_id = 15 /* with GP surgery */ ) ;
Note:
See TracWiki
for help on using the wiki.