Query approver votes of approval steps

From SMTX Wiki
Jump to navigation Jump to search

The query below retrieves all approvers and their votes for a provided ticket id

select pi.[Id], pi.[UniqueReference], pi.[DateStarted] as [ProcessStarted], pi.[DateEnded] as [ProcessEnded], pisl.[Name], pisa.[Email] as [ApproverEmail], pisa.[Name] as [ApproverName], pisia.[DateStarted] as [ApprovalStepStarted], pisia.[DateEnded] as [ApprovalStepEnded], pisia.[DecisionId] as [ApprovalVote] from [ProcessInstance] pi left outer join [ProcessInstanceStep] is on pis.[ProcessInstanceId] = pi.[Id] and pis.[TypeId] = 4 left outer join [ProcessInstanceStepActor] pisa on pisa.[ProcessInstanceStepId]=pis.[Id] and pisa.IsDisabled = 0 left outer join [ProcessISInstanceActor] pisia on pisia.[ProcessInstanceStepActorId]=pisa.[Id] inner join [ProcessInstanceStepLanguage] pisl on pisl.ProcessInstanceStepId = pis.[Id] and pisl.LanguageId = 1 where pi.[Id] = 2744