Been having a bit of an issue creating a report that displays the Top 10 Call Originators based on Site Location. I can get the query to return most of the information but not when I add the WHERE clause. The data is not correct for the date range.
SELECT i.ID, i.Title, i.Originator, i.Status, i.Assignee, i.CreationDate, i.Site, ti.OrigCount
FROM vw_Incident_Site AS i JOIN
(SELECT TOP (10) PERCENT Originator, COUNT(Originator) AS OrigCount
GROUP BY Originator
ORDER BY OrigCount DESC) AS ti ON i.Originator = ti.Originator
WHERE (i.site = 'Site Name') and (CreationDate >= @Start) AND (CreationDate <= @End)
ORDER BY ti.OrigCount DESC, ti.Originator
I am using a view that I have built that returns all incidents based on the colums you want...
Also, when this is displayed in the Report the ordering is totally messed up, not displaying in order 1-10 as per the query.
Any help would be appreciated