Is it possible to select this in one query?

Question

I am writing a program to manage my investments. I am trying to get the total market value of the current holding on a particular date. I have two tables, one is transactions, the other is stock price history.

Transaction Table:

Date       |  action  |   symbol  | qty_change
-------------------------------------------------------------
2016-01-01 |  buy     |    AAPL   |  200
2016-02-01 |  buy     |    GOOG   |  100
2016-07-02 |  sell    |    AAPL   |  -50
2017-02-05 |  sell    |    GOOG   |  -20

The price history table is as follows:

Date       |  symbol  |  colse_price 
------------------------------------------ 
2015-01-01 |  AAPL    |  89.56
2015-01-01 |  GOOG    |  200.00
.....
2016-12-30 |  AAPL    |  102.00
2016-12-30 |  GOOG    |  804.00
2017-03-11 |  AAPL    |  140.00
2017-03-11 |  GOOG    |  850.00

Now I want to get how much market value do I have in 2016-12-31? The result should be like this:

date       |  symbol  |  holding |  close_price |  value 
-----------------------------------------------------------------------
2016-12-31 |  AAPL    |  150     |  102.00      | (holding*close_price)
2016-12-31 |  GOOG    |  100     |  804.00      | 80400.00

I can fulfill a group request, buy, how to join the price?

set @qd = '2016-12-31'
select @qd as query_date, symbol, sum(qty_change) as holding 
    from transaction where date <= @qd 
    group by symbol having holding>0

and please note that the market is close to 2016-12-31, so there is no price for 2016-12-31

Thank.

Answer 1

WHERE, price_history:

set @qd = '2016-12-31';

select t.*, h.close_price, t.holding * h.close_price as `value`
from (
    select t.symbol, sum(t.qty_change) as holding
    from transactions t
    where t.Date <= @qd
    group by t.symbol
) t
join price_history h
    on h.symbol = t.symbol
where h.Date = (
    select max(h1.Date)
    from price_history h1
    where h1.symbol = h.symbol
      and h1.Date <= @qd
)

: http://rextester.com/JTLY66348

Answer 2

:

select @qd as query_date, 
       t1.symbol, 
       sum(qty_change) as holding,
       sum(qty_change) * ph.close_price
from transaction as t1
join price_history as ph on t1.symbol = ph.symbol
left join price_history as ph2 
   on ph.symbol = ph2.symbol and ph2.date > ph.date and ph2.date <= @qd
where t1.date <= @qd and ph.date <= @qd and ph2.date is null
group by t1.symbol 
having holding > 0