4 Replies Latest reply on Jun 25, 2018 11:33 AM by Julie Ide

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

    Julie Ide
    Julie Ide Jun 24, 2018 9:04 PM

      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