Run this SQL script on the RAPCache database at the end of each trading day to capture the last price of that day.
Before running the script, change the date in the two WHERE clauses to the date of the trading day for which the last trade price is to be captured.
Results are stored in the LAST_TRADE_PRICE table and referenced by tick_qry3.sql.
The type of data inserted in the LAST_TRADE_TABLE table by this query is:
INSTRUMENT_ID TRADING_SYMBOL TRADE_PRICE TRADE_DATE 768 BDO 62.96 Nov 10 2005 676 BAA 37.96 Nov 10 2005 419 AQD 28.65 Nov 10 2005 38 ABM 8.03 Nov 10 2005 986 BLY 52.53 Nov 10 2005 332 AMU 17 Nov 10 2005 200 AHS 35.34 Nov 10 2005 104 AEA 19.81 Nov 10 2005 823 BFR 38.53 Nov 10 2005 440 AQY 41.21 Nov 10 2005 ...
-- 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