0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for developers and end users of the Sitecore CMS and multichannel marketing software

;with close_voter as
(
select id
     , postid
     , creationdate
     , text
     , [key]
     , json_value(voters.value, '$.BindingReason.GoldTagBadge') reason
from posthistory ph
cross apply OpenJson(text, '$.Voters') voters
where posthistorytypeid = 10
and comment in (1,101)
and json_value(voters.value, '$.Id') = ##userid:int?337631##
),

total_per_id as
(
select ph.id, count(*) [number_of_voters]
from posthistory ph
inner join close_voter cv on cv.id = ph.id
cross apply OpenJson(ph.text, '$.Voters') voters
group by ph.id
)

select postid
     , [key]
     , [number_of_voters]
     , coalesce(reason, 
       case 
       when number_of_voters < 5 
        and number_of_voters -1 = [key] 
       then 'diamond vote'
       else 'regular vote' 
       end) [reason]
from close_voter cv
inner join total_per_id tpi on tpi.id = cv.id
order by creationdate desc

view current version

edited jun 1

created jun 1 rene