Aggregate function on date difference not working in JRS
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 = 0 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!