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.
DovbermanAsked:
Who is Participating?
 
derekkrommCommented:
actually, the 2nd query isn't right...

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')) or (tradedate < '6/27/07' and exists(select 1 from stockhist where stockid=s.stockid and tradedate = '6/27/07') and pk = (select max(pk) from stockhist where stockid=s.stockid and tradedate < '6/27/07'))
order by stockid,tradedate

that should do the trick
0
 
derekkrommCommented:
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
0
 
DovbermanAuthor Commented:
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,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.