For an Office 2010 Stock Price analysis db I need to implement queries to find:
(1) the Price of a given stock 'x' days before and 'y' days after a reference Trade Date (date being analyzed), and
(2) the dates both Prior To and After the reference Trade Date on which the stock's Closing price was greater than the reference Trade Date Closing price by a specified percent.
Example: AAPL closed at 500.00 on January 18, 2013 (reference Trade Date):
(1) Find the Date and Closing price for 'x' = five days before and 'y' = ten days after Jan. 18. Calendar math does not work since the market is not open on weekends and holidays.
(2) Find the first date prior to Jan. 18 AND the first date after Jan. 18 where AAPL closed 5% higher than it did on Jan. 18.
For requirement (1), I believe a Trade Date Reference Number table would work, where Trade-Date-Ref# = 1 for the first day of historical data held and for each Trade Date thereafter the Ref# is incremented by one.
Any thoughts on alternatives to or suggestions for implementing this Trade-Date-Ref# to keep things simple as possible?
I have basic SQL skills - Starter code for requirement (2) would be much appreciated.
Historic Price table:
Trade Date (key)