SHARE
TWEET

Untitled

a guest Mar 20th, 2019 164 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select  
  2. a.form_id,
  3. a.form_title,
  4. a.question_id,  
  5. a.question_title,
  6. a.option_id,  
  7. a.option_title,
  8. a.option_order,
  9. a.year,
  10. #b
  11. b.response_date,
  12. CASE WHEN b.question IS NULL THEN 0 ELSE 1 END as 'response_count' from  
  13. (    
  14.     select
  15.     z.*,
  16.     y.year    
  17.     from (        
  18.         select DISTINCT q.id as 'question_id',          
  19.         q.title_en as 'question_title',        
  20.         q.deleted_at,        
  21.         o.id as 'option_id',        
  22.         o.title_en as 'option_title',        
  23.         o.order as 'option_order',        
  24.         f.id as 'form_id',        
  25.         f.title_en as 'form_title'        
  26.         from questions  q        
  27.         left join sections s        
  28.         on q.section_id = s.id        
  29.         left join pages p        
  30.         on s.page_id = p.id        
  31.         left join forms f        
  32.         on p.form_id = f.id        
  33.         left join options o        
  34.         on o.question_id = q.id        
  35.         left join question_types qt        
  36.         on q.question_type_id = qt.id        
  37.         where        
  38.         f.id IN (196)        
  39.         and        
  40.         qt.type IN ('radio')        
  41.         and        
  42.         q.deleted_at is null    
  43.     ) z    
  44.     cross join (        
  45.         select distinct YEAR(fr.submitted_at) as 'year'        
  46.         from form_responses fr        
  47.         where fr.submitted_at IS NOT NULL        
  48.         AND YEAR(fr.submitted_at) > '2017'    
  49.     ) y
  50. ) a  left join
  51. #b
  52. (    
  53.     select      
  54.     f.id as 'form_id',    
  55.     q.id as 'question_id',    
  56.     o.id as 'option_id',    
  57.     q.title_en as 'question',    
  58.     o.title_en as 'response',      
  59.     o.order as 'option_order',    
  60.     fr.submitted_at as 'response_date'      
  61.     from question_responses qr    
  62.     left join options o    
  63.     on qr.value = o.id      
  64.     left join form_responses fr    
  65.     on qr.form_response_id = fr.id      
  66.     inner join questions q    
  67.     on qr.question_id = q.id      
  68.     left join question_types qt    
  69.     on q.question_type_id = qt.id    
  70.     left join sections s    
  71.     on q.section_id = s.id    
  72.     left join pages p    
  73.     on s.page_id = p.id    
  74.     left join forms f    
  75.     on p.form_id = f.id    
  76.     where      
  77.     f.id IN (196)    
  78.     and    
  79.     qt.type IN ('radio')    
  80.     and    
  81.     q.deleted_at is null    
  82.     order by option_order
  83. ) b on  
  84. b.question_id = a.question_id AND
  85. b.option_id = a.option_id AND
  86. b.form_id = a.form_id AND
  87. a.year = YEAR(b.response_date)
  88. ORDER BY
  89. a.form_id,
  90. a.option_id,
  91. b.response_date
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top