It's all about the answers!

Ask a question

Aggregate function on date difference not working in JRS


hani zaidi (112) | asked Sep 22 '16, 7:15 a.m.
  Hi,

I've to calculate Average (CR’ Closed Time – CR’s Open Time). I am using CLM V6.0.2 with MSSQL DB and custom queries enabled in JRS. Its a relatively simple query where I sum all the date differences as per the formula and count them against owner. Problem is that this query works perfectly on the same database using sql management studio but fails in JRS. Is there a workaround for this type of report? 

Please find the JRS query below

SELECT T1.PROJECT_NAME,

       T1.REQUEST_TYPE,

       T2.FULL_NAME,

       SUM( DATEDIFF(DAY,T1.CREATION_DATE,T1.RESOLVED_DATE))/COUNT(T2.FULL_NAME) AS DIFF

          

FROM RIDW.VW_REQUEST T1

LEFT OUTER JOIN RIDW.VW_RESOURCE T2

ON T2.RESOURCE_ID=T1.OWNER_ID

WHERE T1.PROJECT_ID =AND

(  T1.REQUEST_TYPE = 'Change Request'

) AND T1.REQUEST_STATE = 'Closed' AND

(T1.ISSOFTDELETED = 0) AND

(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL)GROUP BY T1.PROJECT_NAME,

         T1.REQUEST_TYPE,

         T2.FULL_NAME



Thanks!

Be the first one to answer this question!


Register or to post your answer.