Query for task base executions per agent

[Klaus Lintz]

Hi Team

We have a requirement to report on task base executions per agent.

We do have the standard query for task based executions per month.

The needs this for their recovery cost within the company.

Below is the Oracle query

SELECT 
    TO_CHAR(ah_timestamp2, 'YYYY-MM') AS yearmonth,
    COUNT(COALESCE(AH_idnr, EH_AH_idnr)) AS usage 
FROM 
    AH
LEFT JOIN 
    EH ON AH_Idnr = EH_AH_Idnr
WHERE 
    AH_HostDst IN (
        SELECT 
            OH_NAME 
        FROM 
            OH
        INNER JOIN 
            HOST ON OH_IDNR = HOST_OH_IDNR 
        WHERE 
            HOST_HTYP_SW NOT IN ('AVALOQAGENT', 'BS2000')
    )
    AND (
        AH_Status IN (1900, 1904) 
        OR EH_Status IN (1900, 1904)
    )
    AND AH_OType IN ('JOBS', 'JOBF') 
    AND AH_Timestamp2 BETWEEN TO_DATE('01-JUL-23', 'DD-MON-YY') AND TO_DATE('01-JUL-24', 'DD-MON-YY')
GROUP BY 
    TO_CHAR(ah_timestamp2, 'YYYY-MM')
ORDER BY 
    TO_CHAR(ah_timestamp2, 'YYYY-MM');

Thank you in advance

Klaus Lintz

Cape Town