Alright, I've made some modifications which I've highlighted and at least for my view and state, the results match. I'm sure there will be more exception cases, but I'll adjust . I'm pasting it here with the highlighted differences.
1) added the DISTINCT as per your suggestion
2) added a JOIN to a sub-select of the latest CR modification in the syn_change table
3) changed c.EndModifiedTime to vm.EndModifiedTime (nto totally sure the meaning f this column)
Bottom line, it woks for now. I know that my extra join works for CR's that have been modified to be on another view.
At one point was joining on the latest modification of the vm table, but that created other issues, especially for those twho class ID is no longer 'Change'
Let me know what you think. I will still mark your answer as the answer because greatly reduced the result set.
distinct c.ChangeNumber AS [CR #],
p.name AS [Project Name],
v.name AS [View Name],
c.RevisionNumber AS [Revision]
INNER JOIN syn_change c ON vm.CurrentObjectID = c.ID and vm.classid = (select id from ctables where stname = 'Change')
SELECT max(cr.ModifiedTime) as 'ModTime', cr.ChangeNumber as 'CRNum'
FROM syn_Change AS cr
GROUP BY cr.ChangeNumber
) as sub ON (sub.CRNum = c.ChangeNumber AND sub.ModTime = c.ModifiedTime)
INNER JOIN syn_view v ON c.viewid = v.id
INNER JOIN syn_project p ON v.projectid = p.id
vm.EndModifiedTime = 0 AND
vm.DeletedTime = 0 AND
[CR #] ASC
I haven't worked directly in SQL for info in a long, long time, but there's a chance I'll get back to it if only to deploys some reports. I make my own reports in HTML now through hte SDK, but they certainly lack the dynamic aspect of SQL reports. I'll store you updated query for future use.