Link to home
Start Free TrialLog in
Avatar of Dovberman
DovbermanFlag for United States of America

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.
Avatar of derekkromm
derekkromm
Flag of United States of America image

select 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')
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
ASKER CERTIFIED SOLUTION
Avatar of derekkromm
derekkromm
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dovberman

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,