Query executions per Queue with Archive key value

[Klaus Lintz]

Hi Team

I have a SQLI query that list the number of executions per queue for specific time frame.

I require to add the relevant Queue's Archive key 1  

The archive key will contain a Cost Centre for billing purposes

Below is the Oracle query

SELECT 
    ah.ah_client, 
    TO_CHAR(ah.ah_timestamp2, 'YYYY-MM') AS yearmonth,
    COUNT(ah.ah_idnr) AS usage, 
    ah.ah_queue AS queue
FROM AH ah
LEFT JOIN EH eh ON ah.ah_idnr = eh.eh_ah_idnr
WHERE ah.ah_hostdst IN (
    SELECT oh.oh_name
    FROM OH oh
    INNER JOIN HOST host ON oh.oh_idnr = host.host_oh_idnr
    WHERE host.host_htyp_sw NOT IN ('AVALOQAGENT', 'BS2000')
)
AND (ah.ah_status IN (1900, 1904) OR eh.eh_status IN (1900, 1904))
AND ah.ah_otype IN ('JOBS', 'JOBF')
AND ah.ah_client IN ('30')
AND ah.ah_timestamp2 BETWEEN TO_DATE('2025-07-01', 'YYYY-MM-DD') AND TO_DATE('2025-07-07', 'YYYY-MM-DD')
GROUP BY ah.ah_client, TO_CHAR(ah.ah_timestamp2, 'YYYY-MM'), ah.ah_queue
ORDER BY yearmonth;

Any help will be appreciated

Thanks

Klaus

[Bernhard Flegel]

Hello Klaus,

maybe the following query is usefull for you:

with utctimes (starttime, endtime) as 
(
  select 
      CAST(FROM_TZ(CAST(to_date('01.07.2025 00:00:00', 'DD.MM.YYYY HH24:MI:SS') AS TIMESTAMP),sessiontimezone) AT TIME ZONE 'UTC' AS DATE) , 
      CAST(FROM_TZ(CAST(to_date('08.07.2025 23:59:59', 'DD.MM.YYYY HH24:MI:SS') AS TIMESTAMP),sessiontimezone) AT TIME ZONE 'UTC' AS DATE)   
  from dual
)

select ah_client, ah_archive1 , ah_queue, count (*) as "ANZAHL"
from ah
cross join utctimes
where
    ah_otype in ('JOBS','JOBF') and
    ah_status in ('1900' , '1904') and
    ah_timestamp2 >= starttime and
    ah_timestamp2 <= endtime
    
group by ah_client , ah_archive1 , ah_queue
order by ah_archive1;

[Doug Miles]

Hi Klaus,

You only need to add eh.EH_Archive1 both to the SELECT (the one at the top), and to the GROUP BY (near the bottom), making it

SELECT 
    ah.ah_client, 
    TO_CHAR(ah.ah_timestamp2, 'YYYY-MM') AS yearmonth,
    COUNT(ah.ah_idnr) AS usage, 
    ah.ah_queue AS queue,
    eh.EH_Archive1
FROM AH ah
LEFT JOIN EH eh ON ah.ah_idnr = eh.eh_ah_idnr
WHERE ah.ah_hostdst IN (
    SELECT oh.oh_name
    FROM OH oh
    INNER JOIN HOST host ON oh.oh_idnr = host.host_oh_idnr
    WHERE host.host_htyp_sw NOT IN ('AVALOQAGENT', 'BS2000')
)
AND (ah.ah_status IN (1900, 1904) OR eh.eh_status IN (1900, 1904))
AND ah.ah_otype IN ('JOBS', 'JOBF')
AND ah.ah_client IN ('30')
AND ah.ah_timestamp2 BETWEEN TO_DATE('2025-07-01', 'YYYY-MM-DD') AND TO_DATE('2025-07-07', 'YYYY-MM-DD')
GROUP BY ah.ah_client, TO_CHAR(ah.ah_timestamp2, 'YYYY-MM'), ah.ah_queue, eh.EH_Archive1
ORDER BY yearmonth;

Hope this helps,

Doug

------------------------------
Thanks,
Doug
------------------------------