0 Replies Latest reply on Mar 31, 2012 7:52 PM by ranganathan0

    How to tabulate on most recent row for a set of records

      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.

       

      BP1

                     REQ1

                                    EVENT1 INIT

                                    EVENT2 PEND

                                    EVENT3* PROC

                     REQ2

                                    EVENT1* INIT

                     REQ3

                                    EVENT1 INIT

                                    EVENT2* PEND

                     REQ4

                                    EVENT1 INIT

                                    EVENT2 PEND

                                    EVENT3 PROC

                                    EVENT4* COMP

      BP2

                     REQ1

                                    EVENT1* INIT

                     REQ2    

                                    EVENT1 INIT

                                    EVENT2* PEND

       

      and so on..