I'm trying to produce a report that will count the number of ECRs that are older than 30, 60 and 90 days. The query below is for 30 days. It uses the function COUNT() and works perfectly in MS SQL Mgmt Studio. It won't, however, import to Report Generator. (I've confirmed that I have put the SQL in the proper .crp format). Does anyone have ideas on how to rewrite the following query without using COUNT? (PS: I'm an SQL newbie. I'm sure this query can be improved upon.) Can anyone confirm that COUNT() can't be used?
SELECT COUNT(D.filename) AS [> 30 Days]
FROM Status AS S
INNER JOIN Documents AS D ON S.StatusID = D.CurrentStatusID
JOIN TransitionHistory AS TH ON D.DocumentID = TH.DocumentID
JOIN Workflows AS W ON S.WorkflowID = W.WorkflowID
WHERE (W.name = 'ECR Workflow') and (D.deleted = '0') AND (DATEDIFF (dd, TH.Date, GETUTCDATE()) > '30')
Thanks! Julie
Hi Julie,
It seems to work for me, this is my entry in the CRP file:
It reports 199 files, which is the same i get from a SQL query.