John Power

Query number of files in state?

Discussion created by John Power on Aug 11, 2019
Latest reply on Aug 15, 2019 by Charles McGowan

Hi guys, does anyone have an SQL Query for Number of Files sitting in each State of their Workflow?


I have this query:


select T.Name + ' (' + W.Name + ')' as [Transition(Workflow Name)], Count(1) as [Count] from TransitionHistory TH

inner join Transitions T on TH.TransitionID = T.TransitionID

inner join workflows W on W.WorkflowID = T.WorkflowID

where W.WorkflowID > 1

group by W.Name, T.Name

order by 2 Desc

and it will count how many files have gone through a transition, but I'm looking for something that will interrogate the Projects table to count # of files eg.. S.Name AS 'Status Name' as an int


So it would look like:

In work400


Any tips or advice on how to achieve this would be much appreciated