Does anyone know what tables are in the vault database that originate the data shown in the PDM file history window.
I need to frequently query the file lifecycle to define project performance indices. I'm trying in SQL, I did, but it does not come all data as displayed in the hisórico window. I need to get from the inclusion of the file in the vault to the most current version.
See if this does the trick.
--Query to list workflow transitions used by files under specified folder
SELECT Projects.Path + Documents.Filename as [File Name],
TransitionHistory.DocumentID,
TransitionHistory.TransitionNr,
TransitionHistory.RevNr as [Version],
DATEADD(minute,DATEDIFF(minute,GETUTCDATE(),GETDATE()),TransitionHistory.Date) as [Date],
Users.Username,
Workflows.Name + '.' + Transitions.Name as [Workflow and Transition Name],
S2.Name as [From State],
S1.Name as [To State],
TransitionHistory.Comment
FROM TransitionHistory
Inner Join Documents on Documents.DocumentID = TransitionHistory.DocumentID
Inner Join Transitions on Transitions.TransitionID = TransitionHistory.TransitionID
Inner Join Users on Users.UserID = TransitionHistory.UserID
Join Status S1 on S1.StatusID = Transitions.Destination
Join Status S2 on S2.StatusID = Transitions.Source
Join Workflows on Workflows.WorkflowID = Transitions.WorkflowID
Join DocumentsInProjects on TransitionHistory.DocumentID = DocumentsInProjects.DocumentID
Join Projects on DocumentsInProjects.ProjectID = Projects.ProjectID
WHERE NOT S1.Name IS NULL and NOT S2.Name IS NULL --Exclude trantions to and from the initial state
and Projects.Path like '\<enter path without the vault root folder>\%' --<<< Update with full path relative to vault root, % = wildcard
ORDER BY [File Name], TransitionHistory.TransitionNr