Andrey Kotolevskiy

sql query to report. need to see user worked with file last time

Discussion created by Andrey Kotolevskiy on Apr 24, 2020
Latest reply on May 19, 2020 by Damir Galic

Hello! I found this sql query in the internet and it is awsome. But can you help me to improve it? I need one more column, to see which user is modificated file last time?

 

[
ProjectID pProjectID [1] [select folder.]
String TheStartDate [1] [date1: 2014-08-25]
String TheEndDate [1] [date2: 2014-10-25]
]
§Sql
[
Declare @TheStartDate DATE
Declare @TheEndDate DATE

Set @TheStartDate = {TheStartDate}
Set @TheEndDate = {TheEndDate}

SELECT RV.DocumentID as 'ID' ,
MAX (RV.RevNr) as 'last version',
MAX (RV.Date) as 'date of modification',
P.Path + d.Filename as 'path to file'
FROM Revisions RV, Documents D, DocumentsInProjects DIP, Projects P
WHERE P.ProjectID={pProjectID} and
RV.DocumentID=D.DocumentID and
DIP.DocumentID=D.DocumentID and
DIP.ProjectID=P.ProjectID and
RV.Date BETWEEN @TheStartDate AND @TheEndDate
GROUP BY RV.DocumentID, P.Path, D.Filename
Order by 1

]

Outcomes