One Vote Short

19

Please login or register to vote for this query.

(click on this box to dismiss)

Select a list of posts which are one-vote away from a badge

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

declare @userid as int = ##UserId:int?-1##
;

create table #conditions (
    Badge NVarChar(40) not null,
    Rank int not null,
    PostTypeId int not null,
    Score int not null,
    DayDiff int not null,
    First int not null,
    Accepted int not null)
;
insert into #conditions values ('Great Question', 10, 1, 99,  0, 0, 0)
insert into #conditions values ('Great Answer',   20, 2, 99,  0, 0, 0)
insert into #conditions values ('Guru',           30, 2, 39,  0, 0, 1)
insert into #conditions values ('Enlightened',    35, 2,  9,  0, 1, 1)
insert into #conditions values ('Necromancer',    40, 2,  4, 60, 0, 0)
insert into #conditions values ('Good Question',  50, 1, 24,  0, 0, 0)
insert into #conditions values ('Good Answer',    60, 2, 24,  0, 0, 0)
insert into #conditions values ('Nice Question',  70, 1,  9,  0, 0, 0)
insert into #conditions values ('Nice Answer',    80, 2,  9,  0, 0, 0)
insert into #conditions values ('Revival',        90, 2,  1, 30, 1, 0)
-- insert into #conditions values ("", , , )
-- insert into #conditions values ("", , , )
    

select ROW_NUMBER() over (order by Rank, u.DisplayName, p.id) as Seq,
       c.Badge,
       -- c.Rank,
       p.OwnerUserId as [User Link],
       u.DisplayName as [User Name],
       p.id as [Post Link],
       p.Score,
       pt.Name
from Posts p
  inner join #conditions c on p.PostTypeId = c.PostTypeId
  inner join PostTypes pt on p.PostTypeId = pt.Id
  inner join Users u on p.OwnerUserId = u.Id
  left outer join Posts parent on p.ParentId = parent.Id
where p.Score = c.Score
  and (p.OwnerUserId = @userid OR @userid < 0)
  and (   DayDiff = 0
       OR DayDiff <= DateDiff(day, IsNull(parent.CreationDate, p.CreationDate), p.CreationDate))
  and (   Accepted = 0
       OR p.Id = IsNull(parent.AcceptedAnswerId, 0))
  and (   First = 0
       OR NOT EXISTS (select 1
                      from Posts prev
                      where prev.ParentId = p.ParentId
                        and prev.CreationDate < p.CreationDate
                        and prev.PostTypeId = p.PostTypeId))
                        
order by Seq

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results