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:

StateCount
In work400
Checking75
Released12000

 

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

 

 

Thanks!

Outcomes