12

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for finance professionals and academics

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)

fork query

edited aug 11 14

created feb 15 14 rolfl