Firefly MINT Survey Analysis

FireflyMINTSurveyAnalysis.sql

WITH mint_cte 
     ( 
          id, 
          user_identity, 
          questiontext, 
          choicetext, 
          value, 
          minttype, 
          userguid 
     ) 
     AS 
     ( 
                     SELECT DISTINCT q.id, 
                                     a.guid AS userguid, 
                                     q.text AS questiontext, 
                                     c.text AS choicetext, 
                                     CASE 
                                                     WHEN c.text = 'It is not like me at all' THEN 1
                                                     WHEN c.text = 'I am like this only very rarely' THEN 2
                                                     WHEN c.text = 'I am like this occasionally' THEN 3
                                                     WHEN c.text = 'I am like this sometimes' THEN 4
                                                     WHEN c.text = 'This sounds just like me' THEN 5
                                     END AS value, 
                                     CASE 
                                                     WHEN LEFT(q.text, 3) = '10)' THEN 'Linquistic'
                                                     WHEN LEFT(q.text, 3) = '12)' THEN 'Linquistic'
                                                     WHEN LEFT(q.text, 3) = '24)' THEN 'Linquistic'
                                                     WHEN LEFT(q.text, 3) = '25)' THEN 'Linquistic'
                                                     WHEN LEFT(q.text, 3) = '33)' THEN 'Linquistic'
                                                     WHEN LEFT(q.text, 2) = '8)' THEN 'Logical'
                                                     WHEN LEFT(q.text, 3) = '14)' THEN 'Logical'
                                                     WHEN LEFT(q.text, 3) = '27)' THEN 'Logical'
                                                     WHEN LEFT(q.text, 3) = '32)' THEN 'Logical'
                                                     WHEN LEFT(q.text, 3) = '40)' THEN 'Logical'
                                                     WHEN LEFT(q.text, 2) = '2)' THEN 'Visual'
                                                     WHEN LEFT(q.text, 3) = '17)' THEN 'Visual'
                                                     WHEN LEFT(q.text, 3) = '23)' THEN 'Visual'
                                                     WHEN LEFT(q.text, 3) = '29)' THEN 'Visual'
                                                     WHEN LEFT(q.text, 3) = '36)' THEN 'Visual'
                                                     WHEN LEFT(q.text, 2) = '6)' THEN 'Musical'
                                                     WHEN LEFT(q.text, 3) = '13)' THEN 'Musical'
                                                     WHEN LEFT(q.text, 3) = '18)' THEN 'Musical'
                                                     WHEN LEFT(q.text, 3) = '21)' THEN 'Musical'
                                                     WHEN LEFT(q.text, 3) = '34)' THEN 'Musical'
                                                     WHEN LEFT(q.text, 2) = '4)' THEN 'Interpersonal'
                                                     WHEN LEFT(q.text, 2) = '5)' THEN 'Interpersonal'
                                                     WHEN LEFT(q.text, 3) = '11)' THEN 'Interpersonal'
                                                     WHEN LEFT(q.text, 3) = '28)' THEN 'Interpersonal'
                                                     WHEN LEFT(q.text, 3) = '39)' THEN 'Interpersonal'
                                                     WHEN LEFT(q.text, 2) = '9)' THEN 'Intrapersonal'
                                                     WHEN LEFT(q.text, 3) = '19)' THEN 'Intrapersonal'
                                                     WHEN LEFT(q.text, 3) = '20)' THEN 'Intrapersonal'
                                                     WHEN LEFT(q.text, 3) = '31)' THEN 'Intrapersonal'
                                                     WHEN LEFT(q.text, 3) = '37)' THEN 'Intrapersonal'
                                                     WHEN LEFT(q.text, 2) = '1)' THEN 'Physical'
                                                     WHEN LEFT(q.text, 3) = '15)' THEN 'Physical'
                                                     WHEN LEFT(q.text, 3) = '26)' THEN 'Physical'
                                                     WHEN LEFT(q.text, 3) = '30)' THEN 'Physical'
                                                     WHEN LEFT(q.text, 3) = '38)' THEN 'Physical'
                                                     WHEN LEFT(q.text, 2) = '3)' THEN 'Naturalistic'
                                                     WHEN LEFT(q.text, 2) = '7)' THEN 'Naturalistic'
                                                     WHEN LEFT(q.text, 3) = '16)' THEN 'Naturalistic'
                                                     WHEN LEFT(q.text, 3) = '22)' THEN 'Naturalistic'
                                                     WHEN LEFT(q.text, 3) = '35)' THEN 'Naturalistic'
                                     END    AS minttype, 
                                     a.guid AS guid 
                     FROM            useranswers a 
                     INNER JOIN      c_questions q 
                     ON              a.c_id = q.id 
                     INNER JOIN      choices c 
                     ON              a.answer = CONVERT(VARCHAR, c.id) 
                     INNER JOIN      components co 
                     ON              q.id = co.c_id 
                     INNER JOIN      pagemakeup pm 
                     ON              co.id = pm.component_id 
                     INNER JOIN      pages p 
                     ON              pm.page_id = p.id 
                     WHERE           a.guid = @userGUID 
                     AND             p.long_title = 'Latest MINT Survey' 
     ) 
SELECT   user_identity AS [User], 
         minttype, 
         Sum(value) AS mintvalue 
FROM     mint_cte 
GROUP BY user_identity, 
         minttype 
ORDER BY user_identity, 
         mintvalue DESC";