Voting Trend - Questions Only

0

Please login or register to vote for this query.

(click on this box to dismiss)

Game Development

Q&A for professional and independent game developers

select * from (
  select
    (case
      when q.Score < 0 then 'Voted down'
      when q.Score = 0 then 'Zero'
      else 'Voted up'
    end) + ' | ' + (case
      when q.ClosedDate is not null then 'Closed'
      when a.MaxScore > 0 or q.AcceptedAnswerId is not null then 'Open | Answered'
      else 'Open | Not answered'
    end) as [Status],
    DATEADD(MONTH, DATEDIFF(MONTH, 0, q.CreationDate), 0) as [CreationMonth],
    count(q.Id) as [Question Count]
  from Posts q
    left outer join (
      select ParentId, max(Score) MaxScore
      from Posts
      where PostTypeId = 2
      group by ParentId
    ) a on q.Id = a.ParentId
  where q.PostTypeId = 1
  group by
    (case
      when q.Score < 0 then 'Voted down'
      when q.Score = 0 then 'Zero'
      else 'Voted up'
    end) + ' | ' + (case
      when q.ClosedDate is not null then 'Closed'
      when a.MaxScore > 0 or q.AcceptedAnswerId is not null then 'Open | Answered'
      else 'Open | Not answered'
    end),
    DATEADD(MONTH, DATEDIFF(MONTH, 0, q.CreationDate), 0)
) as [s]
pivot
(
  sum([Question Count])
  for [Status] in (
    [Voted down | Closed], [Voted down | Open | Not answered], [Voted down | Open | Answered],
    [Zero | Closed], [Zero | Open | Not answered], [Zero | Open | Answered],
    [Voted up | Closed], [Voted up | Open | Not answered], [Voted up | Open | Answered])
) as [pivot]
where [CreationMonth] <> DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate()), 0)
order by CreationMonth

Enter Parameters

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