AnsweredAssumed Answered

SolidWorks PDM SQL query

Question asked by David Hales on Oct 10, 2018
Latest reply on Oct 10, 2018 by Clark Honzik

I'm far from a DBA, which is why I have been tying for a couple days to figure out how to get the info that I need.  This is the query I have so far.  I want to add the date of the last version (TransitionHistory.Date).  I thought I could join Documents.LatestRevisionNo = TransitionHistory.RevNr, but I can't get it to work.  I end up getting a huge groupwise maximum and can't overcome it.  Can anyone help?

 

SELECT d.Filename,ISNULL(u.FullName,'') AS CheckedOutBy,fe.Extension AS FileType,s.Name AS State

FROM Documents d

    INNER JOIN Users u ON u.UserID = d.UserID

    INNER JOIN FileExtension fe ON fe.ExtensionID = d.ExtensionID

    INNER JOIN Status s ON s.StatusID = d.CurrentStatusID

    INNER JOIN Workflows w ON w.WorkflowID = s.WorkflowID

WHERE w.WorkflowID <> '1'

    AND d.Deleted = '0'

    AND d.ExtensionID IN ('3','4','5')

    AND d.Filename NOT LIKE '%^%'

    AND d.Filename NOT LIKE '%<%'

ORDER BY d.Filename

Outcomes