HP ALM/QC 11.52, Oracle Database
The user wants to report on old projects and archive projects that have not been logged into for some time.
A. Find the total number of projects in the database:
select * from QCSITEADMIN_DB.projects;
B. Find the number of projects that HAVE been logged into within the past year:
select distinct h.project_name, domain_name from qCSITEADMIN_DB.SESSIONS_HISTORY h;
C. Find the number of users who have not logged in within the past year (note, a bug was discovered where the Username was sometimes lowercase in the Sessions_History table so if using an oracle database, make sure to UCASE or UPPER the usernames).
select * from QCSITEADMIN_DB.projects p where p.PROJECT_NAME not in (select distinct h.project_name, h.domain_name from qCSITEADMIN_DB.SESSIONS_HISTORY h;)
D. Validate Results - Take the number of records from step A and subtract the total records from step B. This number should give you the total number of records to expect in step C.