Finance Times Series - SQL Server - Extract Ordered Data from multiple columns when some columns are NULL

Question

Imagine the following financial trade and quote data in an MS SQL Server table called "tblPrices".

SeqNo       Ticker     TradeType    JulianTime     Price    BidPrice    AskPrice    
1           CLK4       Quote        735706.11      NULL     103         NULL
2           CLK4       Quote        735706.59      NULL     NULL        105
3           CLK4       Trade        735706.59      103      NULL        NULL

NULL is used to conserve space in the table. When extracting 'TradeType' data of value 'Trade', I also want to extract the most recent 'BidPrice' and 'AskPrice' which is not NULL based on JulianTime.

ie. Output for SeqNo 3 above could be:

    JulianTime  TradeType   Price   BidPrice    AskPrice
    735706.59   Trade       103     103          105

Note: SeqNo is an identity of step size 1. JulianTime is a numerical value for DateTime invented by MATLAB.

How can I do this?

You sample data doesn't quite match the desired results. – Mihai Jul 27 '14 at 07:13 — Mihai, Jul 27 '14 at 07:13

score 0 · Answer 1 · edited Jul 27 '14 at 07:45

0

SELECT Ticker, 
       Min(CASE 
             WHEN TradeType = 'Trade' 
                  AND Price IS NOT NULL THEN JulianTime 
           END), 
       Min(Price), 
       Min(BidPrice), 
       Max(AskPrice) 
FROM   tblPrices 
GROUP  BY Ticker

edited Jul 27 '14 at 07:45

Gidil

4,137
2
34
50

answered Jul 27 '14 at 07:20

Mihai

26,325
7
66
81

score 0 · Answer 2 · edited Jul 27 '14 at 12:34

WITH cte 
     AS (SELECT ticker, 
                juliantime, 
                tradetype, 
                price 
         FROM   tblprices 
         WHERE  tradetype = 'Trade') 
SELECT cte.ticker, 
       cte.juliantime, 
       cte.tradetype, 
       cte.price, 
       bid.bidprice, 
       ask.askprice 
FROM   cte 
       OUTER apply (SELECT TOP 1 t1.bidprice 
                    FROM   tblprices t1 
                    WHERE  t1.ticker = cte.ticker 
                           AND t1.bidprice IS NOT NULL 
                           AND t1.juliantime <= cte.juliantime 
                    ORDER  BY t1.juliantime DESC) bid 
       OUTER apply (SELECT TOP 1 t1.askprice 
                    FROM   tblprices t1 
                    WHERE  t1.ticker = cte.ticker 
                           AND t1.askprice IS NOT NULL 
                           AND t1.juliantime <= cte.juliantime 
                    ORDER  BY t1.juliantime DESC) ask

@Gidil: Thank you for formatting the query! Appreciate it. – Lmu92 Jul 27 '14 at 12:52 — Lmu92, Jul 27 '14 at 12:52

score 0 · Answer 3 · answered Jul 27 '14 at 13:52

You are under a misconception - the NULL values consume both more space and more CPU than an equivalent design that disallows nulls. every tick on the ticker is recording an action that is only one of BID, ASK, and SALE so the appropriate table design is

CREATE TABLE tblPrices(
     ID           int identity not null primary key
    ,Ticker       varchar(10)  not null references tblTicker(Code)
    ,TradeType    char(3)      not null -- must be one of BID, ASK, SLD
    ,JulianTime   datetime     not null
    ,Price        money        not null
)

With this table design your query becomes

elect
     JulianTime
    ,Price
    ,(select top 1 Price 
      from tblPrices bid 
      where bid.JulianTime < sld.JulianTime
        and bid.Ticker     = sld.Ticker
        and bid.TradeType  = 'BID'
      order by JulianTime desc
     ) as BidPrice
    ,(select top 1 Price 
      from tblPrices bid 
      where bid.JulianTime < sld.JulianTime
        and bid.Ticker     = sld.Ticker
        and bid.TradeType  = 'ASK'
       order by JulianTime desc
    ) as AskPrice
from tblPrices  sld
where sld.TradeType = 'SLD';

Finance Times Series - SQL Server - Extract Ordered Data from multiple columns when some columns are NULL

3 Answers3