3 Replies Latest reply on May 17, 2016 10:54 AM by Tim Webb

    Report Generator - SQL Query help?

    David Heverin

      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      ********************/

      §Name [Show latest revision number for files]

      §Company [SolidWorks]

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

      §Version [1.0]

      ProjectID PID[1] [Select starting folder]

      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: