How to break a audit and history table to find the nth event after nth event in SQL

I have a audit table in SQL that simply contains some narrative, the datestamp and a linkid that links narratives to a particular object. When reading this table I would search by object id and then order the narratives by date to understand what is happeneing.

Obviously this approach does not work programmatically so I wanted to know how I could build logic to identify specific events.

For example a single object may have multiple narratives that say 'Received new host ...'. And it may have multiple narratives that say 'Matched against object xxx...'.

I would like to partition (?) this table in a way that I can see what the 1st 'Matched against ...' time after the nth 'Receive new ...' record was.

Is this possible?

• The fields in the table are

TradeRef - unique PK and irrelevant
TradeId - the grouped trade ID which is what I will be using
AuditDate
AuditNarrative

• Sample Data

The following is an example of the data for one particular trade Id. I have shown the date and the narrative so you can work out what is happening. To link this back to my question - I want to find out the 1st match that happened after the 2nd 'Received new host contract' but ignore where the end of the narrative is PHONE_CONFIRMED. So simply reading through the records the record I want is the 'Matched event...' record on 29/09/15 15:09:23. Please note that this is a simple example and there are other trades with many more match events and new host contracts coming in!

13/07/15 14:49:00 Received new host contract
13/07/15 15:22:39 Moved to Priority 'Host Unmatched' Queue
13/07/15 19:17:44 Received new counterparty contract
13/07/15 19:21:04 Automated perfect match C123
13/07/15 19:21:04 Automated perfect match H123
13/07/15 19:21:05 Match Event generated for Host Trade H123 and Counterparty Trade C123 C123
12/08/15 15:36:18 Automated unmatch request
12/08/15 15:36:18 Unmatched Event generated for Host trade: H123 and Counterparty Trade: C123
12/08/15 15:36:18 Automated unmatch request
12/08/15 15:36:18 Processed Cancellation
12/08/15 15:42:25 Moved to Priority 'Host Unmatched' Queue
12/08/15 16:00:44 Cpty cancelled their trade stoday. Emailed James Newman to advise
12/08/15 16:23:23 Received cancelled host contract
12/08/15 16:23:23 Processed Cancellation
12/08/15 16:24:04 Received new host contract
12/08/15 16:57:24 Moved to Priority 'Host Unmatched' Queue
17/08/15 21:57:58 Matched Event generated for Host Trade H123 PHONE_CONFIRMED
17/08/15 21:57:58 Manual VCO x
17/08/15 21:57:58 previously mathced Contact:x Contact No:x Contact Date:x Our Phone No: x
17/08/15 22:40:14 Manual unmatched
17/08/15 22:40:14 Unmatched Event generated for Host trade: H123 and Counterparty Trade: PHONE_CONFIRMED
17/08/15 22:42:21 Moved to Priority 'Host Unmatched' Queue
10/09/15 22:01:53 Matched Event generated for Host Trade H123 PHONE_CONFIRMED
10/09/15 22:01:53 Manual VCO x
10/09/15 22:01:53 Affirmations affirmed via email Contact:x Contact No:x Contact Date:x Our Phone No: x
29/09/15 15:09:23 Matched Event generated for Host Trade H123 confirm received
29/09/15 15:09:23 Manual counterparty sight paired confirm received