We tried this query and it works fine. May be you can give a try with it.
This gives the overall status for each client based on the client numbers you have set in the query and for the previous day.
select distinct (AH_Client),
CASE
WHEN AH_STATUS = '1800' THEN 'ENDED_NOT_OK - Aborted'
WHEN AH_STATUS = '1802' THEN 'ENDED_JP_ABEND - Not executed because of abnormal workflow end.'
WHEN AH_STATUS = '1810' THEN 'ENDED_VANISHED - Disappeared'
WHEN AH_STATUS = '1815' THEN 'ENDED_LOST - Ended undefined (host terminated prematurely)'
WHEN AH_STATUS = '1820' THEN 'FAULT_OTHER - Start impossible. Other error.'
WHEN AH_STATUS = '1822' THEN 'FAULT_ALREADY_RUNNING - Task is already running'
WHEN AH_STATUS = '1823' THEN 'FAULT_POST_PROCESSING - Error in post processing'
WHEN AH_STATUS = '1850' THEN 'ENDED_CANCEL - Manually canceled.'
WHEN AH_STATUS = '1851' THEN 'ENDED_JP_CANCEL - Workflow canceled manually.'
WHEN AH_STATUS = '1852' THEN 'Rejected'
WHEN AH_STATUS = '1856' THEN 'ENDED_ESCALATED - Aborted due to escalation'
WHEN AH_STATUS = '1900' THEN 'ENDED_OK - Ended normally'
WHEN AH_STATUS = '1904' THEN 'ENDED_ROLLBACKED - Task was rolled back successfully'
WHEN AH_STATUS = '1905' THEN 'ENDED_WF_ROLLBACKED - The workflow was rolled back successfully.'
WHEN AH_STATUS = '1910' THEN 'ENDED_EMPTY - Task is empty (STOP NOMSG)'
WHEN AH_STATUS = '1912' THEN 'ENDED_EMPTY - Nothing found'
WHEN AH_STATUS = '1913' THEN 'ENDED_ROLLBACK_EMPTY - No rollback defined.'
WHEN AH_STATUS = '1920' THEN 'ENDED_INACTIVE - Inactive today because of the calendar.'
WHEN AH_STATUS = '1922' THEN 'ENDED_INACTIVE - Task has manually been set inactive.'
WHEN AH_STATUS = '1930' THEN 'ENDED_SKIPPED - Skipped because of WHEN clause.'
WHEN AH_STATUS = '1933' THEN 'ENDED_SKIPPED - Skipped because of conditions'
WHEN AH_STATUS = '1940' THEN 'ENDED_TIMEOUT - Not executed because of timeout (WHEN clause).'
WHEN AH_STATUS = '1942' THEN 'ENDED_TIMEOUT - Ended untimely.'
WHEN AH_STATUS = '1944' THEN 'ENDED_TIMEOUT - Period stopped'
ELSE 'Others'
END AS TEST , COUNT(AH_STATUS) as COUNT
from AH
where AH_CLIENT IN (<Client Number>) AND convert (DATE, AH_TimeStamp1) = CONVERT(DATE, (SELECT DATEADD(dd, 0, DATEDIFF(dd, 1, GETDATE())))) and AH_STATUS !='0' group by AH_Client , AH_STATUS
order by AH_Client asc
Sample output as follows
Client | Object Type | Count of Executions |
1001 | ENDED_NOT_OK - Aborted | 8 |
1001 | ENDED_OK - Ended normally | 135715 |
1002 | ENDED_NOT_OK - Aborted | 50 |
1002 | ENDED_OK - Ended normally | 60413 |
1003 | ENDED_OK - Ended normally | 39402 |
1005 | ENDED_OK - Ended normally | 789 |