Q&A about the site for expert genealogists and people interested in genealogy or family history
declare @days as int = ##Days:int?90## declare @topx as int = ##TopX:int?8## --declare @userorange as nvarchar(40) = ##UserOrange:string## --declare @usercyan as nvarchar(40) = ##UserCyan:string?Cyan## --declare @userred as nvarchar(40) = ##UserRed:string?Red## --declare @usergreen as nvarchar(40) = ##UserGreen:string?Green## --declare @userpurple as nvarchar(40) = ##UserPurple:string?Purple## declare @nexus as date = dateadd(day, - @days, CURRENT_TIMESTAMP) declare @userid as int declare @userrep as int declare @username as nvarchar(40) declare @epoch as date = dateadd(day, - 1, @nexus) declare @pdate as date declare @rdate as date declare @rtime as datetime declare @raction as nvarchar(10) declare @rrep as int declare @rcnt as int declare @rtmp as int declare @message as nvarchar(250) declare @cumulative as int declare @rollingrep as int declare @rollingbonus as int declare @rollingcap as int declare @pivot as NVarchar(MAX) declare @query as NVarchar(MAX) ; create table #USERS ( UserId int not null, Name NVarchar(40) not null, Reputation int not null ) ; create table #REPDAY ( UserName nvarchar(40), RepDate date not null, RepWeek date not null, UserRep int not null, CapLimited int not null, UnCapped int not null, Discarded int not null, Cumulative int not null) ; create table #results ( RepDate DATE, Name NVarchar(50), Reputation int ) ; create table #DATES ( EventDate Date not null) ; select @pdate = DateAdd(dd, 1 - DatePart(dw, current_timestamp), Convert(date, current_timestamp)); while @pdate > @epoch begin select @pdate = DateAdd(dd, -1, @pdate); insert into #DATES values (@pdate); end insert into #USERS select TOP ##TopX## u.Id as UserId, '''' + u.DisplayName + '''' as Name, u.Reputation as Reputation from Users u where Reputation > 101 order by Reputation DESC SET @pivot = STUFF((SELECT ',' + QUOTENAME(Name) FROM #USERS FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') print @pivot declare TOPUSERS cursor for select * from #USERS open TOPUSERS fetch next from TOPUSERS into @userid, @username, @userrep while @@FETCH_STATUS = 0 begin -- for each user, reset the accumulators select @pdate = @epoch, -- something different @rcnt = 0, @rollingrep = 0, @rollingbonus = 0, @rollingcap = 0, @cumulative = 0 declare USERVOTES cursor for -- NOTE: convert to DATE truncates the Time-part!!! select convert(DATE, tstamp) as tdate, tstamp, action, rep from ( --Approved suggested edits. select se.ApprovalDate as tstamp, 'edit' as action, 2 as rep from SuggestedEdits se where se.OwnerUserId = @userid and se.ApprovalDate is not null and se.ApprovalDate >= @nexus UNION ALL -- Up and Down votes on Q's and A's select v.CreationDate as tstamp, 'invote' as action, case when v.VoteTypeId = 1 then 15 when v.VoteTypeId = 2 then p.PostTypeId * 5 -- cheeky 5 or 10 for question/answer when v.VoteTypeId = 3 then -2 when v.VoteTypeId = 9 then IsNull(v.BountyAmount, 0) else 0 end as rep from Posts p, Votes v where v.PostId = p.Id and p.OwnerUserId = @userid and p.PostTypeId in (1,2) and v.VoteTypeId in (1,2,3,9) and v.CreationDate > @nexus UNION ALL -- Bounties that were offered.... select v.CreationDate as tstamp, 'bounty' as action, -1 * v.BountyAmount as rep from Votes v where v.VoteTypeId = 8 and v.UserId = @userid and v.CreationDate > @Nexus UNION ALL -- Blank date events select EventDate as tstamp, 'dummy' as action, 0 as rep from #DATES ) as tdata order by tstamp open USERVOTES fetch next from USERVOTES into @rdate, @rtime, @raction, @rrep while @@FETCH_STATUS = 0 begin if @pdate <> @rdate and @rcnt > 0 begin -- break point, new day, save old day's data select @cumulative = @cumulative + @rollingrep + @rollingbonus insert into #repday values (@username, @pdate, @pdate, @userrep, @rollingrep, @rollingbonus, @rollingcap, @cumulative) -- reset our accumulators select @rollingrep = 0, @rollingcap = 0, @rollingbonus = 0, @pdate = @rdate end select @rcnt = @rcnt + 1 if @rrep > 10 or @raction = 'bounty' -- things that score > 10 (accept) are not capped - assume bonus never <= 10 select @rollingbonus = @rollingbonus + @rrep else -- things that score 10 or less are subject to cap select @rollingrep = @rollingrep + @rrep --if @rollingbonus < 0 --begin -- mo --end if @rollingrep > 200 begin -- last action passed the cap... but how much past? select @rtmp = @rollingrep - 200 -- set the value back to 200, and add the difference to the lost rep select @rollingrep = 200, @rollingcap = @rollingcap + @rtmp select @message = 'Vote Maxed ' + Convert(Varchar(20), @rdate) + ' ' + @raction + ' rep ' + Convert(varchar(3), @rrep) + ' ' + Convert(varchar(3), @rtmp) + ' rep wasted' --print @message end fetch next from USERVOTES into @rdate, @rtime, @raction, @rrep end close USERVOTES deallocate USERVOTES -- save away the last day's values that were not break-processed select @cumulative = @cumulative + @rollingrep + @rollingbonus insert into #repday values (@username, @rdate, @rdate, @userrep, @rollingrep, @rollingbonus, @rollingcap, @cumulative) select @message = 'Replayed user ' + @username + ' with rep ' + Convert(varchar(10), @cumulative) + ' and repcnt ' + Convert(varchar(10), @rcnt) print @message fetch next from TOPUSERS into @userid, @username, @userrep END close TOPUSERS deallocate TOPUSERS ; with RepAdjust as ( select UserName as UserName, UserRep - Cumulative as Adjust from #REPDAY out where not exists ( select 1 from #REPDAY sub where sub.UserName = out.UserName and sub.RepDate > out.RepDate) ), UserReputation as ( select rd.RepDate as RepDate, rd.UserName as Name, rd.Cumulative + ra.Adjust as Reputation from #REPDAY rd, RepAdjust ra where ra.UserName = rd.UserName ) insert into #results select * from UserReputation --select * from #results set @query = 'select * from #results pivot (sum(Reputation) for Name in (' + @pivot + ')) as Pivoted order by RepDate ASC' ; print @query ; execute(@query)