6

Please login or register to vote for this query.

(click on this box to dismiss)

Report on all days in which a user loses reputation, or earns 200 rep or more. If you specify -1 as the user, then it reports for all users on the whole site who have ever earned the mortarboard badge.

Q&A for those using, extending or developing Emacs

declare @userid as int = ##UserId##

declare @userrep as int
declare @epoch as date = '1 jan 2099'
declare @pdate as date
declare @rdate as date
declare @rtime as datetime
declare @raction as varchar(10)
declare @rrep as int
declare @rcnt as int
declare @rtmp as int
declare @message as varchar(250)
declare @rollingrep as int
declare @rollingbonus as int
declare @rollingcap as int
;

create table #REPDAY (
    UserId int not null,
    RepDate DATE not null,
    CapLimited int not null,
    UnCapped int not null,
    Discarded int not null)
;

declare TOPUSERS CURSOR FOR
    select Id,
           Reputation
    from Users
    where Id = @userid
    order by Reputation DESC
    
open TOPUSERS

FETCH NEXT from TOPUSERS into @userid, @userrep

WHILE @@FETCH_STATUS = 0
BEGIN

    select @rollingrep = 0
    select @rollingcap = 0
    select @pdate = @epoch
    select @rcnt = 0
    select @rollingrep = 0
    select @rollingbonus = 0
    select @rollingcap = 0

    
    declare USERVOTES CURSOR FOR
    select Convert(DATE, tstamp) as tdate, tstamp, action, rep
      from (
        select se.ApprovalDate as tstamp, 'edit' as action, 2 as rep
        from SuggestedEdits se
        where se.OwnerUserId = @userid
          and se.ApprovalDate is not null
      UNION ALL
        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 = 8 then v.BountyAmount
                   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,8)
      UNION ALL
        select v.CreationDate as tstamp, 'bounty' as action, v.BountyAmount as rep
        from Votes v
        where v.VoteTypeId = 9
          and v.UserId = @userid
          ) 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
            insert into #repday values (@userid, @pdate, @rollingrep, @rollingbonus, @rollingcap)
            select @rollingrep = 0, @rollingcap = 0, @rollingbonus = 0, @pdate = @rdate
        end
        
        select @rcnt = @rcnt + 1
        
        if @rrep <= 10 
            select @rollingrep = @rollingrep + @rrep
        else
            select @rollingbonus = @rollingbonus + @rrep
        
        if @rollingrep > 200
        begin
            select @rtmp = @rollingrep - 200
            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
    
    insert into #repday values (@userid, @rdate, @rollingrep, @rollingbonus, @rollingcap)
    
    select @message = 'Replayed user ' + Convert(varchar(10), @userid) 
                    + ' with rep ' + Convert(varchar(10), @userrep)
                    + ' and repcnt ' + Convert(varchar(10), @rcnt)
    print @message

    FETCH NEXT FROM TOPUSERS into @userid, @userrep
END

close TOPUSERS
deallocate TOPUSERS

select UserId as [User Link],
       RepDate,
       CapLimited as [Actual Regular],
       UnCapped as [Accepts Bonuses],
       CapLimited + UnCapped as [Day Rep],
       Discarded as [Lost Rep]
from #REPDAY
where CapLimited + UnCapped >= 200
   or Discarded > 0

view current version

edited jan 31 14

created jan 31 14 rolfl