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

]