AnsweredAssumed Answered

SQL Count() works on MS SQL Mgmt Studio but not Report Generator.  Any ideas

Question asked by Julie Ide on Jun 22, 2018
Latest reply on Jun 25, 2018 by Julie Ide

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

Outcomes