1 Reply Latest reply on Jun 6, 2018 11:00 AM by Jesse Meiring

    SQL EPDM Report Old File States

    Jesse Meiring

      I'm trying to write a report that will display all EC in a folder that were transitioned through MFG Review in a specified date range. I'm having an issue with is determining weather a file went through a state in the last two weeks. I can find the files and their current states but they have often already been moved to another state within the two weeks I want to look at.

       

      1) How do I check when a file was moved through a certain state?

      2) I'm also getting multiples of each file and I'm not sure how I only display the most up to date one

       

      @[ECNs SUBMITTED]

      §Name [ECs Moved through MFG Review in given timeframe]

      §Company [Wurtec]

      §Description

      [This query will return docm files moved through MFG Review State through given date range.]

      §Version [1.0]

       

      §Arguments

      [

      ProjectID pProjId[1] [Select folder, i.e. Documents or browse for a folder.]

      String stdate[1] [Enter start date as M/D/YYYY, i.e. 1/1/2017.]

      String eddate[1] [Enter start date as M/D/YYYY, i.e. 1/1/2017.]

      ]

       

       

      §Sql

      [

      declare @stdate nvarchar(15)

      set @stdate = {stdate}

      declare @eddate nvarchar(15)

      set @eddate = {eddate}

      SELECT  Documents.Filename, S.Name, TransitionHistory.Date

      FROM    TransitionHistory INNER JOIN

      Documents ON TransitionHistory.DocumentID = Documents.DocumentID INNER JOIN

      DocumentsInProjects ON Documents.DocumentID = DocumentsInProjects.DocumentID INNER JOIN

      Transitions ON Transitions.TransitionID = TransitionHistory.TransitionID INNER JOIN

      Status As S ON Documents.CurrentStatusID = S.StatusID

      WHERE   DocumentsInProjects.Deleted = 0 AND

      Documents.Deleted = 0 AND

      TransitionHistory.Date BETWEEN CONVERT(DATETIME, @stdate , 102) AND

      DATEADD(d,1,@eddate) AND

      Documents.Filename LIKE '%.docm'

      ORDER BY TransitionHistory.Date

      ]

        • Re: SQL EPDM Report Old File States
          Jesse Meiring

          Ended up identifying the TransitionID of the Transition I was looking for then adding it as a addendum to the WHERE section:

          @[ECNs SUBMITTED]

          §Name [ECs Moved through MFG Review in given timeframe]

           

           

          §Company [Wurtec]

           

           

          §Description

          [This query will return docm files moved through MFG Review State through given date range.]

           

           

          §Version [1.0]

           

           

          §Arguments

          [

          ProjectID pProjId[1] [Select folder, i.e. Documents or browse for a folder.]

          String stdate[1] [Enter start date as M/D/YYYY, i.e. 1/1/2017.]

          String eddate[1] [Enter start date as M/D/YYYY, i.e. 1/1/2017.]

          ]

           

           

          §Sql

          [

          declare @stdate nvarchar(15)

          set @stdate = {stdate}

          declare @eddate nvarchar(15)

          set @eddate = {eddate}

          SELECT  Documents.Filename, S.Name, TransitionHistory.Date, TransitionHistory.TransitionID, Documents.DocumentDescription

          FROM    TransitionHistory INNER JOIN

          Documents ON TransitionHistory.DocumentID = Documents.DocumentID INNER JOIN

          DocumentsInProjects ON Documents.DocumentID = DocumentsInProjects.DocumentID INNER JOIN

          Transitions ON Transitions.TransitionID = TransitionHistory.TransitionID INNER JOIN

          Status As S ON Documents.CurrentStatusID = S.StatusID

          WHERE   DocumentsInProjects.Deleted = 0 AND

          Documents.Deleted = 0 AND

          TransitionHistory.Date BETWEEN CONVERT(DATETIME, @stdate , 102) AND

          DATEADD(d,1,@eddate) AND

          Documents.Filename LIKE '%.docm' AND

          TransitionHistory.TransitionID = 52

          ORDER BY TransitionHistory.Date

          ]