Filtering the change date in historical trends reports
Hello Everyone,
I am experiencing an issue when using the report builder for historical trends.
I am getting the limitation warning saying the report results exceeded the 3000 items.
By check the table results, I figured out that in the change date column, the updates are happening each day and every 30 mins, that is why the results exceed by far the 3000 limit. See below example:
I would like to filter the request by just taking the information at end of day, let's say between 23pm and midnight for example. Is it possible to do it from the SQL query for the variable T3.REC_DATETIME:
SELECT T3.REC_DATETIME AS DATETIME,
T1.ACTUAL_WORK AS ACTUAL_WORK1,
COUNT( DISTINCT T1.REFERENCE_ID) AS REFERENCE_ID,
COUNT( DISTINCT T2.REFERENCE_ID) AS REFERENCE_ID1
FROM RICALM.VW_RQST_HISTORY T1
INNER JOIN RIDW.VW_REQUEST T2
ON (T2.REQUEST_ID = T1.REQUEST_ID) AND T2.PROJECT_ID = 27 AND T2.ISSOFTDELETED = 0
INNER JOIN RICALM.VW_RQST_BASELINE T3
ON T1.REQUEST_HISTORY_ID=T3.REQUEST_HISTORY_ID
WHERE T1.PROJECT_ID = 27 AND
( T1.REQUEST_TYPE = 'Task' AND
MONTHS_BETWEEN(CAST(CURRENT_TIMESTAMP AS DATE), CAST(T3.REC_DATETIME AS DATE)) <= 6 AND
CAST(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(T3.REC_DATETIME AS DATE) AS INTEGER) >= 0 AND
( T1.REFERENCE_ID = '20479' )
) AND
T1.ISSOFTDELETED = 0 AND
(T1.REQUEST_HISTORY_ID <> -1 AND T1.REQUEST_HISTORY_ID IS NOT NULL) AND
(T2.REQUEST_ID <> -1 AND T2.REQUEST_ID IS NOT NULL)GROUP BY T3.REC_DATETIME,
T1.ACTUAL_WORK
Is there any other possibility?
Thank you very much for your support.
Omar
|
2 answers
Sorry Omar, My SQL knowledge is not that good. I will leave it to someone else on the forum to help with that.
|
Your answer
Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.