Query to determine which reviewers have voted on a Review Activity
Hi there, I'm trying to get a query working to help our CAB operations, to see which reviewers have not yet voted on a particular RA. I am finding this is a bit of a puzzle that is testing my pretty meagre SQL skills. I have been working on something based heavily on Brett Moffet's very helpful blog post that (I think) should surface the required data from the WorkItem and WorkItem$Review tables within the ServiceManagement db. But, it only appears to work some of the time and I'm not sure what I'm doing wrong.
This is the query I'm playing with:
, IIF(WorkItem.ClassId = 'bfd90aaa-80dd-0fbb-6eaf-65d92c1d8e36', ReviewerCIUser.DisplayName, WorkItem.AssignedUser) as AssignedUser
outer apply (
select top 10 * from ServiceManagement.dbo.WorkItem$Review as ReviewObjects
where ReviewObjects.ReviewActivityId = WorkItem.Id
and ReviewObjects.ReviewerId is not null
order by ReviewObjects.ReviewId Desc --newer reviewers are more likely to have a person, instead of the OOB blank entry.
) as ReviewObjects
left join ServiceManagement.dbo.CI$User as ReviewerCIUser on ReviewerCIUser.ID = ReviewObjects.ReviewerId
where ParentWorkItemId like '%cr781022%'
Looking in the Portal, a number of people have reviewed this RA:
but the query results have null entries:
I'd appreciate any insight you may have