Q&A about the site for developers and end users of the Sitecore CMS and multichannel marketing software
With a as ( Select p.Id, DATEPART(YYYY, p.CreationDate) TimeYr, p.PostTypeId, case when VoteTypeId = 2 then 1.00 when VoteTypeId = 3 then -1.00 else 0.00 end VoteS, DATEDIFF(day, p.CreationDate, v.CreationDate) delay from Votes v join Posts p on p.Id = v.PostId where p.PostTypeId in (1) and v.VoteTypeId in (2,3) and DATEPART(YYYY, p.CreationDate) in (2010,2012,2014,2016) ), c as ( Select DATEPART(YYYY, CreationDate) TimeYr, count(Id) nyear from Posts where PostTypeId = 1 group by DATEPART(YYYY, CreationDate) ), d as ( Select floor(1.00*delay/30) mdelay, TimeYr, SUM(VoteS) sumscore from a group by floor(1.00*delay/30), TimeYr ) Select d.mdelay+1, CAST(d.TimeYr as Char) year, --d.sumscore, --c.nyear, (d.sumscore/c.nyear) averagescore from d join c on d.TimeYr = c.TimeYr where d.mdelay >= 0 and d.mdelay <= 36 order by d.TimeYr, d.mdelay