## One Vote Short

18

(click on this box to dismiss)

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

Q&A for people studying math at any level and professionals in related fields

``````declare @userid as int = ##UserId:int?-1##;
declare @tagname as NVarchar(60) = ##Tag:string?all##;

declare @tagid as int = null;

if lower(@tagname) <> 'all' begin
select @tagid = id
from Tags
where Lower(TagName) = lower(@tagname)
end

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,
Populist int not null)
;
insert into #conditions values ('Populist',        1, 2,  0,  0, 0, 0, 0, 0, 0, 1)
insert into #conditions values ('Reversal',        5, 1, -4,  0, 0, 0, 0, 0, 1, 0)
insert into #conditions values ('Reversal',        6, 2, 19,  0, 0, 0, 0, 1, 0, 0)
insert into #conditions values ('Great Answer',   15, 2, 99,  0, 0, 0, 0, 0, 0, 0)
insert into #conditions values ('Great Question', 20, 1, 99,  0, 0, 0, 0, 0, 0, 0)
insert into #conditions values ('Guru',           30, 2, 39,  0, 0, 1, 0, 0, 0, 0)
insert into #conditions values ('Good Answer',    40, 2, 24,  0, 0, 0, 0, 0, 0, 0)
insert into #conditions values ('Good Question',  50, 1, 24,  0, 0, 0, 0, 0, 0, 0)
insert into #conditions values ('Enlightened',    60, 2,  9,  0, 1, 1, 0, 0, 0, 0)
insert into #conditions values ('Necromancer',    65, 2,  4, 60, 0, 0, 0, 0, 0, 0)
insert into #conditions values ('Nice Answer',    70, 2,  9,  0, 0, 0, 0, 0, 0, 0)
insert into #conditions values ('Nice Question',  75, 1,  9,  0, 0, 0, 0, 0, 0, 0)
insert into #conditions values ('Revival',        80, 2,  1, 30, 1, 0, 0, 0, 0, 0)
insert into #conditions values ('Self-Learner',   85, 2,  2,  0, 0, 0, 1, 0, 0, 0)
-- insert into #conditions values ("", , , )
-- insert into #conditions values ("", , , )

;

with Locked as (
select distinct PostId
from PostHistory ph
where PostHistoryTypeId = 14
and not exists (select 1
from PostHistory sub
where sub.PostId = ph.PostId
and sub.PostHistoryTypeId = 15
and sub.CreationDate > ph.CreationDate)
)
select TOP 1000
ROW_NUMBER() over (order by Rank, DateDiff(week, u.LastAccessDate, CURRENT_TIMESTAMP), p.id desc) as Seq,
-- c.Rank,
u.DisplayName as [User Name],
u.Reputation as [User Rep],
p.CreationDate as [Created],
DateDiff(week, u.LastAccessDate, CURRENT_TIMESTAMP) as [Weeks],
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.id not in (select PostId from Locked)
and IsNull(p.ParentId, 0) not in (select PostId from Locked)
and exists (
select 1
from PostTags
where TagId = IsNull(@tagid, TagId)
and PostId = IsNull(p.ParentId, p.Id)
)
and (     c.Populist = 0
AND p.Score = c.Score
OR c.Populist = 1
AND p.Score > 22
AND parent.id is not null
AND p.Score = 2 * (select Score
from Posts
where parent.id is not null
and PostTypeId = 2))
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 not exists (select 1
where UserId = p.OwnerUserId
and Name = 'Self-Learner')))
OR IsNull(parent.Score, 0) <= -5)
OR exists (select 1
from Posts a
where a.ParentId = p.Id
and a.PostTypeId = 2
and a.Score >= 20))
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