Effectiveness of What Have You Tried

0

Please login or register to vote for this query.

(click on this box to dismiss)

Interpersonal Skills

Q&A for people looking to improve their interpersonal communication skills

SELECT 
       'What Have You Tried' Type,
       Count(c.postid)    QuestionCount, 
       Count(a.id)        AnswerCount_ByCommenter, 
       Sum(CASE 
             WHEN p.closeddate IS NOT NULL THEN 1 
             ELSE 0 
           END)           Closed_Count, 
           Avg(cast (p.answercount as decimal)) Avg_AnswerCount 
FROM   comments c 
       INNER JOIN posts p 
               ON c.postid = p.id 
       LEFT JOIN posts a 
              ON p.id = a.id 
                 AND a.owneruserid = c.userid 
WHERE  p.posttypeid = 1 
       AND c.text LIKE '%WhatHaveYouTried%' 
        OR c.text LIKE '%what have you tried%' 
UNION ALL 
SELECT 
       'Any Comments' Type,
       Count(c.postid)    QuestionCount, 
       Count(a.id)        AnswerCount_ByCommenter, 
       Sum(CASE 
             WHEN p.closeddate IS NOT NULL THEN 1 
             ELSE 0 
           END)           Closed_Count, 
      Avg(cast (p.answercount as decimal))  Avg_AnswerCount 
FROM   (SELECT DISTINCT userid, 
                        postid 
        FROM   comments) AS c 
       INNER JOIN posts p 
               ON c.postid = p.id 
       LEFT JOIN posts a 
              ON p.id = a.id 
                 AND a.owneruserid = c.userid 
WHERE  p.posttypeid = 1 
UNION ALL 
SELECT 
       'Any Questions' Type,
       Count(p.id)    QuestionCount, 
       NULL AnswerCount_ByCommenter, 
       Sum(CASE 
             WHEN p.closeddate IS NOT NULL THEN 1 
             ELSE 0 
           END)           Closed_Count, 
        Avg(cast (p.answercount as decimal)) Avg_AnswerCount 
FROM   
       posts p 
       
WHERE  p.posttypeid = 1

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results