13 Replies Latest reply: Jun 2, 2014 3:45 PM by Patrick Tabin RSS

    Script to get final piece of data

    Rayna Curtis

      I have a view that accesses phone call records.  The call can be answered, transferred, sent to voicemail etc, and each action adds a record to the table.  The session id marks the call and each sequence number marks each action, so a call that gets transferred around will have the same sessionID but can (and usually does) have several sequence numbers.

       

      I need to be able to just look at the call status of that *last* sequence number to see how the call ended.

       

      I have tried getting the last record and then using that to join back to a second instance of the table to get the last activity state, but it does not work.  I still get all actions, not just the last one.

       

      lastActivity:

      Load

      SessionID,

      max(SequenceID) as "Last Activity"

      group By SessionID;

       

      SQL SELECT *

      FROM database

       

      lastActivityState:

      Load

      SessionID,

      SequenceID as "Last Activity",

      ExitState as "Last Activity State"

      ;

       

      SQL SELECT *

      FROM database

       

      I tried max(exitState) but, of course, that gives me the highest exist state numerically which is not what I need.  I have also tried to concatenate the session and max sequence ID to get a single key, but that does not work either.  But I am willing to live with the synthetic key, if I can just get it to work.

       

      Can anyone please offer any direction?

       

      Thank you!