troubleshooting Question

Need help with transact-SQL

Avatar of Dovberman
DovbermanFlag for United States of America asked on
SQL
3 Comments1 Solution208 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
derekkromm

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros