JRS query to retrieve all projects in RTC which was inactive for past 16 months

Rao Shines (159●4●47●59) | asked Apr 12 '18, 2:50 a.m.
edited Apr 12 '18, 6:06 a.m.

Hi,

We are using ALM 6.0.2 and we have around 600 RTC projects that were created over a period of 8 years, now to like to optimize and get list of all RTC projects that were not been used/active for past 16 months. The JRS query created includes some active projects also, can someone help review the query or provide some effective SQL query for the same.

SELECT DISTINCT T1.PROJECT_NAME

FROM RIDW.VW_REQUEST T1

WHERE (T1.PROJECT_ID = 1285 OR T1.PROJECT_ID = 497 OR T1.PROJECT_ID = 1022 OR T1.PROJECT_ID = 145 OR T1.PROJECT_ID = 1364 OR T1.PROJECT_ID = 66...........) AND

( (T1.REQUEST_TYPE = 'Defect' OR T1.REQUEST_TYPE = 'Story' OR T1.REQUEST_TYPE = 'Task' ) AND

MONTHS_BETWEEN(CAST(CURRENT_TIMESTAMP AS DATE), CAST((T1.REC_DATETIME AS DATE)) > 16 AND

(MONTHS_BETWEEN(CAST(CURRENT_TIMESTAMP AS DATE), CAST((T1.REC_DATETIME AS DATE)) <> 16 OR T1.REC_DATETIME IS NULL)

) AND

(T1.ISSOFTDELETED = 0) AND

(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL)

Thanks in Advance

Accepted answer

permanent link

Amy Silberbauer (306●5●7) | answered Apr 12 '18, 4:42 p.m.
JAZZ DEVELOPER

Hi again,
If I have your criteria correct for what makes a project area obsolete, why wouldn't you just look for project areas where the # of work items updated in the past 16 months = 0? I am running 6.0.6 so have capabilities in JRS that you do not have. I was able to create this report without advanced SQL, but here is the SQL in case you're curious:

SELECT T1.PROJECT_NAME,
COUNT(DISTINCT CASE WHEN (MONTHS_BETWEEN(CAST(CURRENT_TIMESTAMP AS DATE), CAST(T1.REC_DATETIME AS DATE)) <= 16 ) AND (DAYS(CURRENT_TIMESTAMP) - DAYS(T1.REC_DATETIME) <= 0 ) THEN T1.REQUEST_ID END ) AS REC_DATETIME1
FROM RIDW.VW_REQUEST T1
WHERE T1.PROJECT_ID IN (<your project IDs here>)
AND
T1.ISSOFTDELETED = 0 AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL)GROUP BY T1.PROJECT_NAME

This returns the count for each project area of the number of work items (of any type) modified within the past 16 months. If the number is 0, the project area might be considered obsolete based on your rules -- which I am assuming at the moment.
Amy

Rao Shines selected this answer as the correct answer

Comments

Rao Shines commented Apr 13 '18, 6:40 a.m.

Thank you for the query, I was able to get the list of projects.

One other answer

Most liked answers ↑|Newest answers|Oldest answers

permanent link

Amy Silberbauer (306●5●7) | answered Apr 12 '18, 4:14 p.m.
JAZZ DEVELOPER

Hi,
So it looks like you are determining that a project area is inactive based on the fact that no work item of type defect, story or task in a given project has been modified (?) in the last 16 months. Is that right?
Amy

It's all about the answers!

Related questions

JRS query to retrieve all projects in RTC which was inactive for past 16 months

Accepted answer

One other answer

Your answer