T-SQL: Prioritize Results From Multiple Sources

If you are familiar with financial securities, you know they are uniquely identified by their Ticker (i.e. MSFT) and/or their CUSIP. You probably also know that there are dozens of vendors which provide financial data. Let’s say you needed to provide a security’s price from the most reliable source using the most reliable identifier. How would you do it?

Let’s assume we have a single security:

Ticker: JC
CUSIP : 012345678
Security ID: 1 (which is consistent across all sources)

Our requirements state that prices should be retrieved with the following priority:

1. Source A by CUSIP
2. Source B by Ticker
3. Source A by Ticker
4. Source B by CUSIP

What’s the data look like?

In Source A, the security’s price of $10 can be found by CUSIP.
In Source B, the security’s price of $15 can be found by Ticker.
In Source A, the security’s price of $20 can be found by Ticker.
In Source B, the security’s price of $25 can be found by CUSIP.

We took on the problem a couple of ways, but ultimately we found the following provided the best performance and most accurate results.

It doesn’t take long to realize a temporary table (or table variable) is required. In this case, we chose to use the temporary table so an index could be applied. Though the following example is truly “dumbed down,” in real life, there were approximately 20 combinations to handle.

Create Table #Results
(
PriorityID int identity(1,1),
SecurityID int,
Ticker varchar (5) NULL,
CUSIP varchar (9) NULL,
Price money NULL
)

We opted to dump all matches into our temporary table using a giant union.

Insert Into #Results (SecurityID, Ticker, CUSIP, Price)

Select SecurityID, Ticker, Price
From SourceA
Where CUSIP = ’012345678′

Union All

Select SecurityID, Ticker, Price
From SourceB
Where Ticker = ‘JC’

Union All

Select SecurityID, Ticker, Price
From SourceA
Where Ticker = ‘JC’

Union All

Select SecurityID, Ticker, Price
From SourceB
Where CUSIP = ’012345678′

So, #Results would include the following rows:

PriorityID, SecurityID, Ticker, CUSIP, Price
1, 1, null, 012345678, $10
2, 1, JC, null, $15
3, 1, JC, null, $20
4, 1, null, 012345678, $25

We then we returned the row which the lowest PriorityID. This was the first row inserted into the table and was effectively our best price based on priority.

Select SecurityID, Ticker, Price, SortOrder
From #Results
Where PriorityID in ( Select Min( PriorityID ) From #Results group by [SecurityID])

Pretty slick solution, right? Well, we thought so as well, but as it works out there’s a huge flaw. You see, we were working under the assumption that SQL Server would insert the result of the first select statement of the union into the #Results table before moving onto other union sections. Well, we thought wrong. We weren’t able to determine why, but each execution of the routine might return the results in a different order. (Our best guess is that SQL Server operates on a first come – first serve basis. Whichever result comes back first get inserted into the temp table first.)

Today’s implementation is slightly different. We have added a SortOrder column to the #Results table.

Create Table #Results
(
PriorityID int identity(1,1),
SecurityID int,
Ticker varchar (5) NULL,
CUSIP varchar (9) NULL,
Price money NULL,
SortOrder int
)

Insert Into #Results (SecurityID, Ticker, CUSIP, Price, SortOrder )

Select SecurityID, Ticker, Price, 1 as SortOrder
From SourceA
Where CUSIP = ’012345678′

Union All

Insert Into #Results (SecurityID, Ticker, Price, SortOrder )
Select SecurityID, Ticker, Price, 2 as SortOrder
From SourceB
Where Ticker = ‘JC’

Union All

Insert Into #Results (SecurityID, Ticker, CUSIP, Price, SortOrder )
Select SecurityID, Ticker, Price, 3 as SortOrder
From SourceA
Where Ticker = ‘JC’

Union All

Insert Into #Results (SecurityID, Ticker, CUSIP, Price, SortOrder )
Select SecurityID, Ticker, Price, 4 as SortOrder
From SourceB
Where CUSIP = ’012345678′

Order By SortOrder

Now we are inserting security information into the #Results table with consideration for SortOrder and the correct results are consistently returned.

Select SecurityID, Ticker, Price
From #Results
Where PriorityID in ( Select Min( PriorityID ) From #Results group by [SecurityID])

We haven’t measured performance, but slow and accurate is better than fast and inaccurate…