0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

declare @dt1 varchar(25) = convert(varchar,getdate(),21)
declare @dtUpdated datetime = (select min(create_date) from sys.tables)

print @dt1
print @dtUpdated

select   -----------------------------> (list eligible answers)
  creationdate,
  id,
  parentid,
  score
into #eligibleAnswers from posts
where
  owneruserid = ##UserID## --my posts
  and posttypeid = 2       --answers only
  --and deletiondate is null
  --(Deleted questions are ALREADY EXCLUDED from this table)
  and score > 0
  
select -----------------------------> Make a temp tables of relevant #Votes 
id, postid, votetypeid 
into #votes from votes 
where postid in (select id from #eligibleAnswers)
  and votetypeid in (2,3)


select   -----------------------------> (populate #1,#2)
  a.creationdate,
  a.id as aID, --answer ID
  a.parentid,  --question ID
  'site://a/'+cast(a.id as varchar)+'|A:'+q.title as [Link],
  q.viewcount, --question #views
  case when a.id = q.acceptedanswerid then 1 else 0 end as [1of4:IsAccepted],
  a.score,     --answer score
  case when a.score >= 5 then 1 else 0 end as [2of4:ScoreMin5],
  (  select sum(aa.Score) as [Votes:all]
        from posts aa
        where aa.parentid = q.id
     ) as [Votes:all], --total +&- vote count
  --votes +&- on my answer
  (  select a.Score as [Votes:me] ) as [Votes:me]
into #answers2 from #eligibleAnswers a 
  left outer join posts q on a.parentid = q.id
where q.posttypeid = 1 



select -----------------------------> (populate #3)
  [creationdate],    --answer date
  [aID],             --answer ID
  [parentid],        --question ID
  [Link],       --link to answer
  [viewcount],       --question #views
  [1of4:IsAccepted], --my answer = accepted ?
  [score],           --answer score
  [2of4:ScoreMin5],  --score >5 ?
  [Votes:all],       --total answer vote count
  [Votes:me],        --my answer vote count
  case when (cast([Votes:me] as decimal) / [Votes:all]) >= 0.2 
    then 1 else 0 end as [3of4:20pVotes]
into #answers3 from #answers2


--create list of all answers per question
--(to determine if answer is in Top 3)
select
   pall.parentid as [qID], 
   pall.id as [aID], 
   pall.owneruserid, 
   rank() over (partition by pall.parentid order by pall.score desc ) as [Rank]
into #voteRanks
from posts pmine left outer join posts pall
on pmine.parentid = pall.parentid
where pmine.owneruserid=##UserID##
  and pall.parentid is not null
  and pmine.posttypeid=2


--select * from #voteRanks
select * from #answers3
--select * from #eligibleAnswers
--select * from #votes

fork query

edited oct 22 20

created oct 5 20 NearHuscarl