AnsweredAssumed Answered

Query to find locked files in a folder (project)

Question asked by Chris Mueller on Aug 9, 2011
Latest reply on Aug 10, 2011 by Michael Dekoning

I spent a little time trying to aquaint myself with the SQL queries used to gather data from EPDM.  I was able to make some small changes and get the query to run, but I am not skilled enough to create a query that I really want.  Here is the objective:


A query that will return a list of all locked files within a certain project, even files that have never been checked into EPDM (i.e. created on the user's computer, but not ever checked in).  Such files are problematic for me because I cannot see them using the usual EPDM tools, such as search (I'm a local admin and don't have access to the Admin account, from which these files can apparently be seen).  These files are also the files that prevent me ultimately from doing things like changing folder names.


Here is where I started.


@[Checked Out Files]

§Name [Checked Out Documents]


§Company [BiffsBolagAB]



[This query lists all documents checked out.]


§Version [1.0]




String   Days [N] [Enter minimum days to report.]





SELECT  DateDiff(day, D.LockDate, GetDate()+1) as 'Days Checked Out'

                    , U.Username as 'Checked Out By'

                    , D.Filename as 'Document'

                    , D.LockDomain as 'Locked on Computer'

                    , D.LockPath as 'Locked in Folder'

FROM          Documents as D


On D.UserID = U.UserID

And U.UserID <> '1'

WHERE DateDiff(day, D.LockDate, GetDate()+1) >= {days}

And D.Deleted = 0

ORDER BY U.Username, 'Days Checked Out' DESC



This query was written by another forum user to generate a list of checked out files that have been checked out more than a certain number of days.  This query is ROBUST! It returns everything that could even think about being a checked out (locked) file (i.e. even local files on users machines that never have been checked in).


Now I want to remove the condition that the files be checked out for a number of days, but add the condition that they lie within a certain folder (project).  I have seen some other queries where the argument is the ProjectID:




ProjectID pProjectID [1] [Select folder. E.g "$\Documents", or browse for folder.]



And I think that I somehow need to JOIN the projectID with the list created by the query above, right?  But this is where I get stuck due to my lack of SQL experience, programming skills, examples, etc.


Anyone that can help?