プログラマーとプログラミングに熱心な人々のための質問と回答
-- timeframe : Timeframe "yy for year, qq for quarter, mm for month" declare @cutoff integer declare @FromDate datetime = Convert(datetime, '##FromDateYYYYMMDD##'); select top 1 @cutoff = v.id from votes v where creationdate >= @FromDate order by creationdate desc select id, displayname into #users from users where reputation > 200 create index #ix_users on #users(id) ;with user_scores as ( select u.id , sum( case v.votetypeid when 1 then 15 when 2 then 10 when 3 then -2 when 9 then v.bountyamount else 0 end ) [total_rep] from #users u inner join posts p on p.owneruserid = u.id and p.communityowneddate is NULL inner join votes v on v.postid = p.id and votetypeid in (1,2,3,9) where v.id > @cutoff group by u.id ), users_ranked as ( select rank() over(order by [total_rep] desc) [rank] , * from user_scores ) select 'You' , [rank] , concat('site://users/', r.id, '|', u.displayname) [Link] , [total_rep] from users_ranked r inner join #users u on u.id = r.id where r.id = ##userid## union all select 'Community' , [rank] , concat('site://users/', r.id, '|', u.displayname) [Link] , [total_rep] from users_ranked r inner join #users u on u.id = r.id order by 1 desc, [total_rep] desc