Active Project list from ALM/QC

Question ID: 106737
0
0

Is there a way to get a list of active projects not used in last X months in HP ALM/QC?

Marked as spam
Posted by (Questions: 122, Answers: 3)
Asked on March 28, 2016 6:03 pm
49 views
Answers (2)
0
Private answer

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_Used_Projects AS
(SELECT sh.DOMAIN_NAME, sh.PROJECT_NAME, MAX(END_TIME) AS LAST_USAGE
FROM td.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 td.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_Used_Projects AS (SELECT sh.DOMAIN_NAME, sh.PROJECT_NAME, MAX(END_TIME) AS LAST_USAGE FROM tm_alm12_qcsiteadmin_db.SESSIONS_HISTORY sh WHERE END_TIME IS NOT NULL GROUP BY sh.PROJECT_NAME, sh.DOMAIN_NAME) SELECT nup.* FROM Not_Used_Projects nup INNER JOIN PROJECTS p on p.PROJECT_NAME = nup.PROJECT_NAME AND PR_IS_ACTIVE = 'Y' WHERE LAST_USAGE <= ADD_MONTHS(sysdate, -3);

Marked as spam
Posted by (Questions: 3, Answers: 168)
Answered on March 28, 2016 6:04 pm
0
Private answer

Oracle query is throwing an error though I changed ''tm_alm12_qcsiteadmin_db'' to my SA schema name.

ORA-00942: table or view does not exist
00942. 00000 - ''table or view does not exist''
*Cause:
*Action:
Error at Line: 19 Column: 18

I ran entire Oracle query?

Marked as spam
Posted by (Questions: 30, Answers: 35)
Answered on May 22, 2019 5:04 pm