link-mostly answers over time

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

select dateadd(month, datediff(month, '2009-01-01', CreationDate), convert(date, '2009-01-01')) as Month,
count(*) as "Link-mostly answers"

from
(select 
  a.Id as AnswerId,
  a.CreationDate
from
  Posts a
where
  a.PostTypeId = 2
  and Len(a.Body) < ##MaxAnswerLength##
  and a.Body like '%<a href="http://%'

  and (charindex('</a>', a.Body, charindex('<a href="http://', a.Body)+16)-charindex('<a href="http://',a.Body))
  > 
  len(a.Body) - (charindex('</a>', a.Body, charindex('<a href="http://', a.Body)+16)-charindex('<a href="http://',a.Body))  
  and a.CreationDate < '6/1/2013'
  ) x 
  group by dateadd(month, datediff(month, '2009-01-01', CreationDate), convert(date, '2009-01-01'))
  order by dateadd(month, datediff(month, '2009-01-01', CreationDate), convert(date, '2009-01-01')) asc

Enter Parameters

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