2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- My Money for Jam in the last 90 days --
/*
  Works out the daily figure for Non Community Wiki Posts that earn
 the most Passive Reputation.
 
  Reputation gained in the first 15 days of post is ignored,
 all reputation after that is considered passive reputation.
 
  Post must be at least 60 Days old at the start of the 3 month
 period to make the calculation easier on SQL Server. 
 */

set nocount on

declare @earliestDate datetime,
        @latestQuestionPostDate datetime
set     @earliestDate = DateAdd(d, -90, GetDate())
declare @ignoreDays numeric = 15
declare @minAgeDays numeric = @ignoreDays * 4
set     @latestQuestionPostDate = DateAdd(d, -1 * @minAgeDays, @earliestDate)

declare @UserId int = ##UserId##

select sum(case when VoteTypeId = 2.0 then
        case when p.ParentId is null then 5.0 else 10.0 end
        else 0 end) / 90 as dailyUp,
    sum(case when VoteTypeId = 3 then 2.0 else 0.0 end) / 90 as dailyDown
from Votes v join posts p on v.postid = p.id
where v.VoteTypeId in (2,3)
  and v.CreationDate >= @earliestDate
  and OwnerUserId = @UserId
  and p.CommunityOwnedDate is null
  and p.CreationDate < @latestQuestionPostDate

fork query

edited sep 25 13

created sep 25 13 anonymous