Applies To: 

Seeing which projects have been active in the last 3 months

 

Issue Summary:

Is there a way to get a list of active projects not used in last x months in HP Application Lifecycle Management (ALM)/ Quality Center(QC)?

  

Fix:

This could be done with simple SQL query over qcsiteadmin_db database on your DB server.

Note:Please ask your Database Administrator(DBA) for assistance on this.

In the following example is used a 3-months period.

For MS SQL:

WITH Not_Useed_Projects

AS (

SELECT sh.DOMAIN_NAME

, sh.PROJECT_NAME

, MAX(END_TIME) AS LAST_USAGE

FROM SESSIONS_HISTORY AS sh

WHERE END_TIME IS NOT NULL

GROUP BY sh.PROJECT_NAME, sh.DOMAIN_NAME

)

SELECT nup.*

FROM Not_Useed_Projects AS nup INNER JOIN

PROJECTS p on p.PROJECT_NAME = nup.PROJECT_NAME AND PR_IS_ACTIVE = 'Y'

WHERE LAST_USAGE <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -3, 0)

For Oracle:

WITH Not_Useed_Projects

AS (

SELECT sh.DOMAIN_NAME

, sh.PROJECT_NAME

, MAX(END_TIME) AS LAST_USAGE

FROM SESSIONS_HISTORY AS sh

WHERE END_TIME IS NOT NULL

GROUP BY sh.PROJECT_NAME, sh.DOMAIN_NAME

)

SELECT nup.*

FROM Not_Useed_Projects AS nup INNER JOIN

PROJECTS p on p.PROJECT_NAME = nup.PROJECT_NAME AND PR_IS_ACTIVE = 'Y'

WHERE LAST_USAGE <= ADD_MONTH(sysdate, -3)