Summary of the requested processes by process status

Query to get summary of the requested process.

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