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
]
SolidworksSolidworks Pdm enterprise Pdm