Dovberman
asked on
Need help with transact-SQL
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.
ie.
Select all stocks that were traded on 06/27/2007.
Then select the most recent trade date for each stock prior to
06/27/2007.
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.
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.
ie.
Select all stocks that were traded on 06/27/2007.
Then select the most recent trade date for each stock prior to
06/27/2007.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are truely amazing. I did not think that this could be done without a complicated stored procedure.
This is part of the resulting dataset.
PK StockID TradeDate Price
35643 18368 2007-06-26 00:00:00.000 17.06
41083 18368 2007-06-27 00:00:00.000 17.26
34761 18369 2007-06-26 00:00:00.000 3.96
40210 18369 2007-06-27 00:00:00.000 4.00
35430 18370 2007-06-26 00:00:00.000 13.94
40864 18370 2007-06-27 00:00:00.000 13.86
Thanks,
This is part of the resulting dataset.
PK StockID TradeDate Price
35643 18368 2007-06-26 00:00:00.000 17.06
41083 18368 2007-06-27 00:00:00.000 17.26
34761 18369 2007-06-26 00:00:00.000 3.96
40210 18369 2007-06-27 00:00:00.000 4.00
35430 18370 2007-06-26 00:00:00.000 13.94
40864 18370 2007-06-27 00:00:00.000 13.86
Thanks,
from stockhist s
where tradedate='6/27/07' and exists(select 1 from stockhist where stockid=s.stockid and tradedate < '6/27/07')
union
select top 1 pk,stockid,tradedate,price
from stockhist s
where tradedate < '6/27/07' and exists(select 1 from stockhist where stockid=s.stockid and tradedate = '6/27/07')
order by pk desc