Matching Trades and Quotes (MSSQL 2005/2008)
Hi,
I have the following two tables (Trades and Quotes) with sample data as listed below. Since there is no direct correspondence between Trades and Quotes one matches based on one’s criteria. So, based on each Trade, I may have many quotes and I will try and match the best quote.
In this example, I want to match based on the following: Select ONE (1) Quote that is < 5 seconds of the Trade TIME. If there is a TIE then select the most recent one (example TradeID 8 instead of TradeID 7) (while ignoring the others). IF there is no quote at < 5 seconds then select the trade that meets the last quote (Please see TradeID 4 and QuoteID 13).
The expected results denote that you get the QuoteID 6 for TradeID 1 and so on.
p.s. The ID’s are not part of the table and are provided to assist in understanding the issue.
I sincerely would appreciate any assistance -- suggestions as to how to go about writing the code - Cursors, Correlated sub-queries or any sample code that you could throw in. I am using SQL Server 2008 but OK if you provide assistance for SQL Server 2005.
** Trades table **
ID Ticker Date Time Price
1 IBM 20081001 10:00:00 20.00
2 IBM 20081001 10:00:01 20.25
3 IBM 20081001 10:00:06 20.50
4 IBM 20081001 10:15:00 21.00
** Quotes table **
ID Ticker Date Time Price
1 IBM 20081001 09:59:45 19.75
2 IBM 20081001 09:59:46 19.75
3 IBM 20081001 09:59:47 19.85
4 IBM 20081001 09:59:48 19.85
5 IBM 20081001 09:59:50 19.84
6 IBM 20081001 09:59:54 19.85
7 IBM 20081001 09:59:55 19.85
8 IBM 20081001 09:59:55 19.85
9 IBM 20081001 10:00:00 20.25
10 IBM 20081001 10:00:00 20.50
11 IBM 20081001 10:00:00 20.75
12 IBM 20081001 10:10:00 20.50
13 IBM 20081001 10:10:00 20.75
Expected Results -- So the expected results should be that the following quotes will be returned for the following trades.
TradeID QuoteID
1 6
2 8
3 11
4 13
Really appreciate your help and thanks for any advice.