21

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

Q&A for Drupal developers and administrators

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', 1, 1, 99,  0, 0, 0)
insert into #conditions values ('Good Question',  3, 1, 24,  0, 0, 0)
insert into #conditions values ('Nice Question',  5, 1,  9,  0, 0, 0)
insert into #conditions values ('Great Answer',   2, 2, 99,  0, 0, 0)
insert into #conditions values ('Good Answer',    4, 2, 24,  0, 0, 0)
insert into #conditions values ('Nice Answer',    6, 2,  9,  0, 0, 0)
insert into #conditions values ('Guru',           3, 2, 39,  0, 0, 1)
insert into #conditions values ('Revival',        8, 2,  1, 30, 1, 0)
insert into #conditions values ('Necromancer',    3, 2,  4, 60, 0, 0)
-- insert into #conditions values ("", , , )
-- insert into #conditions values ("", , , )
    

select p.id as [Post Link], p.Score, c.Badge, c.Rank, pt.Name
from Posts p
  inner join #conditions c on p.PostTypeId = c.PostTypeId
  inner join PostTypes pt on p.PostTypeId = pt.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))
order by rank

view current version

edited feb 21 14

created feb 21 14 rolfl