troubleshooting Question

Need help with transact-SQL

Avatar of Dovberman
DovbermanFlag for United States of America asked on
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.

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.

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