Testing

0

Please login or register to vote for this query.

(click on this box to dismiss)

Gardening and Landscaping Meta

Q&A about the site for gardeners and landscapers

declare @2018DataInRange table (Years int,Groups varchar(10) ,Days int , HowManyQuestion int) 
 
 
 ;WITH cte AS
      (
         SELECT ParentId,creationdate ,
               ROW_NUMBER() OVER (PARTITION BY ParentId 
               ORDER BY creationdate ASC) AS rn
         FROM posts 
      )
      insert into @2018DataInRange 
      SELECT datepart(year,question.creationdate) Years,
      --ANSWER.creationdate,
      case 
      WHEN DATEDIFF(day, question.creationdate, ANSWER.creationdate) between  0 and 0 then '0-0'
      WHEN DATEDIFF(day, question.creationdate, ANSWER.creationdate) between 1 AND 5 THEN '1-5' 
      WHEN DATEDIFF(day, question.creationdate, ANSWER.creationdate) between 6 AND 10 THEN '6-10'
      WHEN DATEDIFF(day, question.creationdate, ANSWER.creationdate) between 11 AND 20 THEN '11-20'
      WHEN DATEDIFF(day, question.creationdate, ANSWER.creationdate) between 21 AND 51 THEN '21-50' 
      WHEN DATEDIFF(day, question.creationdate, ANSWER.creationdate) between 51 AND 100 THEN '51-100'
      WHEN DATEDIFF(day, question.creationdate, ANSWER.creationdate) between 101 AND 150 THEN '101-150'
      WHEN DATEDIFF(day, question.creationdate, ANSWER.creationdate) between 151 AND 200 THEN '151-200'
      WHEN DATEDIFF(day, question.creationdate, ANSWER.creationdate) between 201 AND 250 THEN '201-250'
      WHEN DATEDIFF(day, question.creationdate, ANSWER.creationdate) between 251 AND 365 THEN '251-365'
      ELSE '251-3808' 
      END as Groups, 
      DATEDIFF(day, question.creationdate, ANSWER.creationdate) ResponseInDays,
      count(*) HowManyQuestion

      FROM cte ANSWER
      INNER JOIN POSTS question
      ON ANSWER.ParentId = question.ID AND datepart(year,ANSWER.creationdate) <= 2018  
      WHERE rn = 1 and DATEDIFF(day, question.creationdate, ANSWER.creationdate) >= 0
      group by datepart(year,question.creationdate),
      DATEDIFF(day, question.creationdate, ANSWER.creationdate)
      
      select Years,cast(Groups as varchar(20)) DataInRange, sum(HowManyQuestion) HowManyQuestion from @2018DataInRange 
      group by Years,Groups

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results