Need help with transact-SQL
I have a SQL table that stores stock closing prices
for each date a stock was traded. There are some days that
a few stocks are not traded.
I need the closing price on a given date and
the closing price on the previous date that the stock was traded.
Select all stocks that were traded on 06/27/2007.
Then select the most recent trade date for each stock prior to
Order the results by StockID and Trade Date
There should be two rows for each StockID.
The table name is StockHist
PK StockID TradeDate Price
35643 18368 2007-06-26 17.06
41083 18368 2007-06-27 17.26
34761 18369 2007-06-24 3.96 '06/24 is most recent trade date prior to 06/27
40210 18369 2007-06-27 4.00
35430 18370 2007-06-26 13.94
40864 18370 2007-06-27 13.86
36223 18371 2007-06-27 31.10 'No trade prior to 06/27/2007
18371 should not be selected.
This is probably done with a sub-select with TOP 2 for each stock
except when the Count is 1.
I am unsure of the required syntax for a single SQL statement or
for a stored procedure.