Changes between Version 9 and Version 10 of Onyx to CiviCRM

Timestamp:

12/12/13 15:51:51 (11 years ago)

Author:

Richard Bramley

Comment:

--

Onyx to CiviCRM

@@ -123 +123 @@
 
 }}}
+
+Try a different approach:
+
+{{{#!sql
+
+SELECT
+      p.id AS ParticipantID
+    , p.barcode AS StudyID
+    , p.first_name
+    , p.last_name
+    , p.birth_date
+    , p.gender
+    , p.enrollment_id AS UhlSystemNumber
+    , consent.q1
+    , consent.q2
+    , consent.q3
+    , consent.q4
+    , consent.q5
+FROM participant p
+LEFT JOIN (
+    SELECT
+          qp.participant_id
+        , GROUP_CONCAT(if(qa.question_name = 'consent_q1', category_name, NULL)) AS q1 
+        , GROUP_CONCAT(if(qa.question_name = 'consent_q1', category_name, NULL)) AS q2
+        , GROUP_CONCAT(if(qa.question_name = 'consent_q1', category_name, NULL)) AS q3
+        , GROUP_CONCAT(if(qa.question_name = 'consent_q1', category_name, NULL)) AS q4
+        , GROUP_CONCAT(if(qa.question_name = 'consent_q1', category_name, NULL)) AS q5
+    FROM question_answer qa
+    JOIN category_answer ca ON ca.question_answer_id = qa.id
+    JOIN questionnaire_participant qp ON qp.id = qa.questionnaire_participant_id
+         AND qp.questionnaire_name = 'ManualConsentQuestionnaire'
+    GROUP BY qp.participant_id
+    ) consent ON consent.participant_id = p.id
+;
+
+}}}