Need help with transact-SQL

Posted on 2007-07-22
Last Modified: 2010-03-20
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.
Question by:Dovberman
    LVL 15

    Expert Comment

    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')
    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
    LVL 15

    Accepted Solution

    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

    Author Comment

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Data lost in SQL server 23 83
    Duplicate + trim records SQL 11 39
    SP to delete duplicates 15 46
    IF SQL Statement Access SQL 6 38
    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now