Outer table reference in sub-select

Question

I have two tables, one that represents stock trades:

Blotter

TradeDate    Symbol   Shares    Price
2014-09-02   ABC      100       157.79
2014-09-10   ABC      200       72.50
2014-09-16   ABC      100       36.82

and one that stores a history of stock splits for all symbols:

Splits

SplitDate    Symbol   Factor
2014-09-08   ABC      2
2014-09-15   ABC      2
2014-09-20   DEF      2

I am trying to write a report that reflects trades and includes what their current split adjustment factor should be. For these table values, I would expect the report to look like:

TradeDate    Symbol   Shares   Price    Factor
2014-09-02   ABC      100      157.79   4
2014-09-10   ABC      200      72.50    2
2014-09-16   ABC      100      36.82    1

The first columns are taken straight from Blotter - the Factor should represent the split adjustments that have taken place since the trade occurred (the Price is not split-adjusted). Complicating matters is that each symbol could have multiple splits, which means I can't just OUTER JOIN the Splits table or I will start duplicating rows.

I have a subquery that I adapted from https://stackoverflow.com/a/3912258/3063706 to allow me to calculate the product of rows, grouped by symbol, but how do I only return the product of all Splits records with SplitDates occurring after the TradeDate?

A query like the following

SELECT tb.TradeDate, tb.Symbol, tb.Shares, tb.Price, ISNULL(s.Factor, 1) AS Factor
    FROM Blotter tb
    LEFT OUTER JOIN (
        SELECT Symbol, EXP(Factor) AS Factor
            FROM
                (SELECT Symbol, SUM(LOG(ABS(NULLIF(Factor, 0)))) AS Factor
                    FROM Splits s
                    WHERE s.SplitDate > tb.TradeDate   -- tb is unknown here
                    GROUP BY Symbol
                ) splits) s 
         ON s.Symbol = tb.Symbol

returns the error "Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "tb.TradeDate" could not be bound."

Without the inner WHERE clause I get results like:

TradeDate    Symbol   Shares   Price    Factor
2014-09-02   ABC      100      157.79   4
2014-09-10   ABC      200      72.50    4
2014-09-16   ABC      100      36.82    4

Update The trade rows in Blotter are not guaranteed to be unique, so I think that rules out one suggested solution using a GROUP BY.

Laurence · Answer 1 · 2014-10-09T23:36:35.007

One way without changing the logic too much is to put the factor calculation into a table valued function:

create function dbo.FactorForDate(
    @Symbol char(4), @TradeDate datetime
) returns table as
return (
    select
        exp(Factor) as Factor
    from (
        select
            sum(log(abs(nullif(Factor, 0)))) as Factor
        from
            Splits s 
        where
            s.SplitDate > @TradeDate and
            s.Symbold = @Symbol
    ) splits
);

select
    tb.TradeDate, 
    tb.Symbol, 
    tb.Shares, 
    tb.Price, 
    isnull(s.Factor, 1) as Factor
from
    Blotter tb
        outer apply
    dbo.FactorForDate(tb.Symbol, tb.TradeDate) s;

To do it in a single statement is going to be something like:

select
    tb.TradeDate, 
    tb.Symbol, 
    tb.Shares, 
    tb.Price, 
    isnull(exp(sum(log(abs(nullif(factor, 0))))), 1) as Factor
from
    Blotter tb
        left outer join
    Symbol s
         on s.Symbol = tb.Symbol and s.SplitDate > tb.TradeDate
group by
    tb.TradeDate, 
    tb.Symbol, 
    tb.Shares, 
    tb.Price;

This will probably perform better if you can get it to work.

Apologies for any syntax errors, don't have access to SQL at the moment.

Interesting idea - I'll give it a try tomorrow at work if there's not a single-query suggestion. — Danny, Oct 09 '14 at 23:55
One problem with that single-query solution is that the rows in Blotter are not guaranteed to be unique. I had cut it down to just some basic fields for the purposes of the question, but even with the full table it's entirely possible that multiple matching trades would happen, so the Factor value would get skewed as a result. — Danny, Oct 10 '14 at 01:18

Outer table reference in sub-select

1 Answers1