Check-in/Check-out report per Project Per Person

Document ID : KB000123013
Last Modified Date : 13/12/2018
Show Technical Document Details
Introduction:
Do you how can we get the Report of check-in/check-out per Person per Project?
Environment:
CA Harvest SCM all versions
Tested on an Oracle database
Instructions:

For files checked out:
SELECT HARENVIRONMENT.ENVIRONMENTNAME,
   HARUSER.USERNAME,
   COUNT(HARVERSIONS.VERSIONOBJID) AS FILESCHECKEDOUT
FROM HARENVIRONMENT
INNER JOIN HARPACKAGE ON HARENVIRONMENT.ENVOBJID = HARPACKAGE.ENVOBJID
INNER JOIN HARVERSIONS ON HARPACKAGE.PACKAGEOBJID = HARVERSIONS.PACKAGEOBJID
INNER JOIN HARUSER ON HARVERSIONS.CREATORID = HARUSER.USROBJID
WHERE HARPACKAGE.PACKAGENAME != 'BASE'
AND HARVERSIONS.ITEMTYPE      = 1
AND HARVERSIONS.VERSIONSTATUS = 'R'
AND HARVERSIONS.CREATIONTIME BETWEEN (SysDate - 7) AND SysDate + 1
GROUP BY HARENVIRONMENT.ENVIRONMENTNAME, HARUSER.USERNAME
 
 For files checked in:
SELECT HARENVIRONMENT.ENVIRONMENTNAME,
   HARUSER.USERNAME,
   COUNT(HARVERSIONS.VERSIONOBJID) AS FILESCHECKEDIN
FROM HARENVIRONMENT
INNER JOIN HARPACKAGE ON HARENVIRONMENT.ENVOBJID = HARPACKAGE.ENVOBJID
INNER JOIN HARVERSIONS ON HARPACKAGE.PACKAGEOBJID = HARVERSIONS.PACKAGEOBJID
INNER JOIN HARUSER ON HARVERSIONS.MODIFIERID      = HARUSER.USROBJID
WHERE HARPACKAGE.PACKAGENAME != 'BASE'
AND HARVERSIONS.ITEMTYPE      = 1
AND HARVERSIONS.VERSIONSTATUS != 'R'
AND HARVERSIONS.MODIFIEDTIME BETWEEN (SysDate - 7) AND SysDate + 1
GROUP BY HARENVIRONMENT.ENVIRONMENTNAME, HARUSER.USERNAME