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

      @[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!!