It's all about the answers!

Ask a question
QuestionsTagsUsersBadges
Guidelines | FAQ
Follow this question
Question details

×42,882
×12,008
×359
×230
×5
×2

question asked: Mar 19 '19, 6:54 a.m.
question was seen: 2,516 times
last updated: Mar 19 '19, 8:22 a.m.

Related questions

Filtering the change date in historical trends reports

0
Omar Khemoudj (23●3●14) | asked Mar 19 '19, 6:54 a.m.

 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:


Change Date (Custom Trends for Work Items)  Actual Work (Custom Trends for Work Items)   Custom Trends for Work Items: Count total number of artifact...   Work Item: Count total number of artifacts  
10/28/18 2:08 AM 28,800 1 1
12/14/18 1:07 AM 28,800 1 1
1/10/19 1:07 AM 28,800 1 1
1/14/19 9:30 AM 28,800 1 1
1/23/19 12:00 PM 28,800 1 1
9/19/18 12:17 AM 28,800 1 1
9/19/18 12:47 AM 28,800 1 1
9/19/18 1:17 AM 28,800 1 1
9/19/18 1:47 AM 28,800 1 1
9/19/18 2:17 AM 28,800 1 1
9/19/18 2:47 AM 28,800 1 1
9/19/18 3:17 AM 28,800 1 1
9/19/18 3:47 AM 28,800 1 1
9/19/18 4:18 AM 28,800 1 1
9/19/18 4:48 AM 28,800 1 1

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

Most liked answers ↑|Newest answers|Oldest answers

0
permanent link
Gerard Cregan (61●3) | answered Mar 19 '19, 7:03 a.m.
Hello Omar,

The 'Data Mart Data Collection' jobs should only be run once a day.
Running it more often does not give you any benefit.
You need to change that for a start.

You should open a Support Ticket to look at removing the extra data in the Data Warehouse.

Comments
Omar Khemoudj commented Mar 19 '19, 7:09 a.m.

Thanks Gerard,


By any chance, can we modify the query to pick up only the items between a time range, let's say between 11pm and midnight as example. 

0
permanent link
Gerard Cregan (61●3) | answered Mar 19 '19, 7:21 a.m.

Sorry Omar, My SQL knowledge is not that good. I will leave it to someone else on the forum to help with that.

Comments
Omar Khemoudj commented Mar 19 '19, 8:22 a.m.

Thanks Gerard, appreciate your help.


 

Your answer

Register or to post 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.