AnsweredAssumed Answered

Report Generator - SQL Query help?

Question asked by David Heverin on Apr 19, 2016
Latest reply on May 17, 2016 by Tim Webb

I have a SQL query (.crp file) that returns a file name, latest revision, workflow status and path.  I want to edit it to also return the latest revision date.  Can anyone look at query and help?

 

Here is the query:

 

/*******************    Enterprise Report      ********************/

@[GetFilenameAndRevision]
§Name [Show latest revision number for files]

§Company [SolidWorks]

§Description
[This report lists the latest revision number for files under selected folder.]

§Version [1.0]

§Arguments
[
ProjectID PID[1] [Select starting folder]
]

§Sql
[
Declare @FolderPath nvarchar(260)
Set @FolderPath = (Select Path from Projects where ProjectID = {PID});
        
WITH GetRevs AS
(
SELECT D.Filename, MAX(UR.Comment) AS Revision
FROM  RevGenCounters AS RGC INNER JOIN
      Documents AS D ON RGC.DocumentID = D.DocumentID INNER JOIN
      UserRevs AS UR ON D.DocumentID = UR.DocumentID
Group By D.Filename
)
Select GR.Filename, GR.Revision, S.Name AS [Workflow Status], P.Path AS [Folder Path]
FROM GetRevs AS GR INNER JOIN Documents AS D ON D.Filename = GR.Filename INNER JOIN
DocumentsInProjects AS DP ON DP.DocumentID = D.DocumentID INNER JOIN
Projects AS P ON P.ProjectID = DP.ProjectID INNER JOIN
Status AS S ON D.CurrentStatusID = S.StatusID
Where P.Path like @FolderPath + '%'

]

/*******************                        ********************/

 

This is the report generated:

 

Thanks!!

Outcomes