Q&A about the site for developers and end users of the Sitecore CMS and multichannel marketing software
DECLARE @minscroe int = ##minscore##; DECLARE @firstgolds int = ##firstgolds##; --DECLARE @UserId int = ##UserId## with gposts as( select postid, creationdate from ( select postid, creationdate, row_number() over (partition by postid order by creationdate) as rnk from votes where votetypeid=2 ) t where rnk = @minscroe), firstng as( select year2, days from ( select datediff(d, p.creationdate, gp.creationdate) as days, year(p.creationdate) as year2, row_number() over (partition by year(p.creationdate) order by p.creationdate) as rnk2 from gposts gp inner join posts p on gp.postid = p.id ) t2 where rnk2 <= @firstgolds) select year2 as [year], avg(days) as days from firstng group by year2 having count(*)=@firstgolds order by year2 return; --firstn as (select year(p.creationdate) as [year], --avg(datediff(d, p.creationdate, gp.creationdate)) as days, --count(*) numgold --from gposts gp inner join posts p on gp.postid = p.id --group by year(p.creationdate) --order by year(p.creationdate) --select year(p.creationdate) as [year], --avg(datediff(d, p.creationdate, gp.creationdate)) as days, --count(*) numgold --from gposts gp inner join posts p on gp.postid = p.id --group by year(p.creationdate) --order by year(p.creationdate)