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