## One Vote Short

19

(click on this box to dismiss)

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

Q&A for programmers

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

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

select ROW_NUMBER() over (order by Rank, u.DisplayName, p.id) as Seq,
-- c.Rank,
u.DisplayName as [User Name],
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
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
and (   Selfie = 0
OR p.OwnerUserId = IsNull(parent.OwnerUserId, 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:
Hold tight while we fetch your results
:records returned in :time ms:cached