SQL

The following script contains the SQL statements for this query.

-- This code should be run at the end of each trading day to capture
-- the last price of that day. Before running the script, the date in
-- the two where clauses needs to be changed to that of the trading day
-- for which the last trade price is to be captured.

Insert LAST_TRADE_PRICE
Select INSTRUMENT_ID, TRADING_SYMBOL, TRADE_PRICE, TRADE_DATE 
FROM  STOCK_TRADE st, (Select INSTRUMENT_ID AS idx, max(TRADE_TIME) AS maxtime,   max(TRADE_SEQ_NBR) AS maxseq
    from STOCK_TRADE 
    where TRADE_TIME between '2005-11-10 00:00:00' and '2005-11-10 23:59:59'
group by INSTRUMENT_ID) y
WHERE  st.TRADE_TIME between '2005-11-10 00:00:00' and '2005-11-10 23:59:59'
AND st.TRADE_TIME = maxtime and idx = st.INSTRUMENT_ID and st.TRADE_SEQ_NBR = maxseq
go

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF