I have an Oracle (currently using 10g express) table containing all the business events for multiple processes and their requests. Each request has multiple rows corresponding to activity events.
I need to get the current status for each request and aggregate this at the business process level. The pattern is like below. Each event has a timestamp.
I need to aggregate by business process (BP1, BP2 etc) by the current event (denoted by * below) status (INIT / PEND / PROC / COMP).
BP INIT PEND PROC COMP
BP1 1 1 1 1
BP2 1 1
Is there a way to do this in PA Studio without doing some custom SQL? I tried that too but ran into difficulties.
and so on..