Matching Trades and Quotes (MSSQL 2005/2008)

[sqlsrvrnewbie]

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.

[MAK]

use tempdb
go
drop table trade
drop table quotes
create table trade(id int, ticker varchar(10), date datetime,price money)
insert into trade select 1 ,'IBM' ,'20081001 10:00:00' ,20.00
insert into trade select 2 ,'IBM' ,'20081001 10:00:01' ,20.25
insert into trade select 3 ,'IBM' ,'20081001 10:00:06' ,20.50
insert into trade select 4 ,'IBM' ,'20081001 10:15:00' ,21.00

create table quotes (id int, ticker varchar(10), date datetime,price money)

insert into quotes select 1 ,'IBM','20081001 09:59:45', 19.75
insert into quotes select 2 ,'IBM','20081001 09:59:46', 19.75
insert into quotes select 3 ,'IBM','20081001 09:59:47', 19.85
insert into quotes select 4 ,'IBM','20081001 09:59:48', 19.85
insert into quotes select 5 ,'IBM','20081001 09:59:50', 19.84
insert into quotes select 6 ,'IBM','20081001 09:59:54', 19.85
insert into quotes select 7 ,'IBM','20081001 09:59:55', 19.85
insert into quotes select 8 ,'IBM','20081001 09:59:55', 19.85
insert into quotes select 9 ,'IBM','20081001 10:00:00', 20.25
insert into quotes select 10 ,'IBM','20081001 10:00:00', 20.50
insert into quotes select 11 ,'IBM','20081001 10:00:00', 20.75
insert into quotes select 12 ,'IBM','20081001 10:10:00', 20.50
insert into quotes select 13 ,'IBM','20081001 10:10:00', 20.75
go

Try this.

select id,ticker,date,price=(select top 1 convert(varchar(10),qu.id)
+','+convert(varchar(10),qu.price)+','+convert(var char(30),qu.date,109)
from quotes qu where dateadd(s,-6,tr.date)=date and ticker=tr.ticker )
from trade tr