Summary of requested processes by process status

The following query will give you a summary of the requested processes by process status.

SELECT
RQST.RUNSTATUS,
RQST.PRCSTYPE,
(
SELECT XLAT.XLATLONGNAME
FROM PSXLATITEM XLAT
WHERE XLAT.EFFDT = (
SELECT MAX(XLAT_ED.EFFDT)
FROM PSXLATITEM XLAT_ED
WHERE XLAT_ED.FIELDNAME = XLAT.FIELDNAME
AND XLAT_ED.FIELDVALUE = XLAT.FIELDVALUE
) AND XLAT.FIELDNAME = 'RUNSTATUS'
AND XLAT.FIELDVALUE = RQST.RUNSTATUS
) AS RUNSTATUS_XLAT,
COUNT(RQST.PRCSINSTANCE) AS TOTAL_PROCESSES,
MIN(RUNDTTM) AS FIRST_OCCURRED,
MAX(RUNDTTM) AS LAST_OCCURRED
FROM PSPRCSRQST RQST
GROUP BY RQST.RUNSTATUS, RQST.PRCSTYPE
ORDER BY RUNSTATUS_XLAT, RQST.PRCSTYPE