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

how to solve below logic? using sql server?

Ι have 3 columns in my table: col1, col2 and col3.

col2 is having (1,      0,   2,   3,   4,   0,   1,   3) 
col3 is having (1234,1023,1025,1032,1234,1013,1325,1332) 

Ι have to get col1 values as

(1234,1234,1234,1234,1234,1013,1013,1013). 

Τhe logic is i have to get col3 value for col1 untill the same number repeated in column two.

Please give me any suggestion to solve this.

Prompt for SQL dates

I have written a query that returns the data from SQL Server 2005, but I need a prompt for users (Excel) to select date range.

SQL Server - Need to check spelling errors in a column

Looking for a quick help. In Sql, one of the table has a column(ntext). I need to find the count of spelling mistakes in the field. Association of MS Word dictionary would be great, however, can work with any other dictionary as well.

Any help on this would be great.

How to build a SQL Insert Query Method in C#

I want know the trick to create method which perform the Insert operation on the database based on n number of parameters

here is pseudocode definition

Insertdata(tablename,Col_name,Col_values)

add data to db

output: can be normal sql query string or insert operation on Database;

How to handle replication post SQL Server 2000/2008 migration?

I have two servers; server A host SQL Server 2000 and server B ( a remote server) host SQL Server 2005. I replicate data between these two servers using transactional replication. I recently migrated (side by side) the SQL server 2000 to SQL Server 2008 hosted on a virtual server. I would like to mimic the replication I already have between server A abd B. Can someone tell me the best practice of carrying out this task? Essentially, what I want to do is to set up a transactional replication between the SQL Server 2008 hosted on a virtual server and SQL Server 2005 hosted server B ( a remote server) . Do I have to create all the publications and subscriptions from scratch ? Thanks for your contribution in advance.

How to Convert String into Time format?

I have a varchar column in sql server 2005 which stores time as 1420 but I want this to be shown as 14:20 or 02:20 PM in report. Please help..