0

Please login or register to vote for this query.

(click on this box to dismiss)

Adapted from Jon's original query, this allows you to define lookback period and shows votes per week rather than per day.

Q&A for scientists using computers to solve scientific problems

--declare @start datetime = (
--select top 1 dateadd(day, datediff(day, getdate(), CreationDate), getdate())
--from Posts
--group by datediff(day, getdate(), CreationDate)
--having count(*) >= ##betathreshold?10##
--order by datediff(day, getdate(), CreationDate)
--);

declare @stop datetime = (
select top 1 dateadd(day, datediff(day, getdate(), CreationDate), getdate())
from Posts
group by datediff(day, getdate(), CreationDate)
having count(*) >= ##betathreshold##
order by datediff(day, getdate(), CreationDate) desc
);

declare @start datetime = dateadd(month, -##lookbackmonths##, @stop)

select dateadd(week, datediff(week, @start, CreationDate), @start) "Week of",
       --vt.Name,
       CASE WHEN IsAnonymous = 1 THEN 'Anonymous '+vt.Name ELSE 'Registered '+vt.Name END "Vote Type",
       count(*) "Daily Average"
from PostFeedback
     join VoteTypes vt on vt.Id = VoteTypeId
where CreationDate between @start and @stop
      --and IsAnonymous = 0
group by datediff(week, @start, CreationDate), vt.Name , IsAnonymous
order by datediff(week, @start, CreationDate), count(*) desc

fork query

edited sep 30 Catija

created nov 12 14 Jon "not doe" Ericson