Solved

Self Join Help

Posted on 2013-12-02

1 solution

Medium Priority

313 Views

Last Modified: 2013-12-02

I have the following table:

Need to display this value
StockID SymbolID QuoteDate ClosePrice Prev Price
28707405 2        2013-10-17 00:00:00.000      1.34
28715071        2        2013-10-21 00:00:00.000      1.37 1.34
28705598 3        2013-10-17 00:00:00.000      1.31
28713345        3        2013-10-21 00:00:00.000      1.35 1.31
28705599        5        2013-10-17 00:00:00.000      15.22
28713346        5        2013-10-21 00:00:00.000      15.11 15.22

I need a SQL statement that displays the PrevPrice for 10-21 from the ClosePrice of 10-17
as shown above.

I have tried the following statements:

SELECT TOP 30 StockHistID,SymbolID, QuoteDate, ClosePrice,
(SELECT ClosePrice FROM StockHist WHERE SymbolID = 15 AND QuoteDate = '2013-10-17') AS PrevPrice
FROM StockHist WHERE QuoteDate BETWEEN '2013-10-17' AND '2013-10-21' ORDER BY SymbolID,QuoteDate

Sub Select
28707405      2      2013-10-17 00:00:00.000      1.34      49.99
28715071      2      2013-10-21 00:00:00.000      1.37      49.99
28705598      3      2013-10-17 00:00:00.000      1.31      49.99
28713345      3      2013-10-21 00:00:00.000      1.35      49.99
28705599      5      2013-10-17 00:00:00.000      15.22      49.99
28713346      5      2013-10-21 00:00:00.000      15.11      49.99

I may need a self join but am not sure how to do this.

Thanks

Comment

Question by:Dovberman

9 Comments

LVL 11

Expert Comment

by:Angelp1ay

ID: 396893232013-12-02

I think you just need this?

SELECT s21.SymbolId, s21.PrevPrice, s17.ClosePrice 
FROM StockHist s21 
INNER JOIN StockHist s17 ON s21.SymbolId = s17.SymbolId
WHERE s21.QuoteDate = '2013-10-21'
AND s17.QuoteDate = '2013-10-17'

Open in new window

LVL 11

Expert Comment

by:Angelp1ay

ID: 396893292013-12-02

I figure you want to compare to check they are the same value...

Comparison as a column:

SELECT s21.SymbolId, s21.PrevPrice, s17.ClosePrice, 
        AreSame =  CASE WHEN s21.PrevPrice = s17.ClosePrice THEN '1' ELSE '0' END
FROM StockHist s21 
INNER JOIN StockHist s17 ON s21.SymbolId = s17.SymbolId
WHERE s21.QuoteDate = '2013-10-21'
AND s17.QuoteDate = '2013-10-17'

Open in new window

Filter showing mismatches:

SELECT s21.SymbolId, s21.PrevPrice, s17.ClosePrice
FROM StockHist s21 
INNER JOIN StockHist s17 ON s21.SymbolId = s17.SymbolId
WHERE s21.QuoteDate = '2013-10-21'
AND s17.QuoteDate = '2013-10-17'
AND s21.PrevPrice <> s17.ClosePrice

Open in new window

Author Comment

by:Dovberman

ID: 396894162013-12-02

A modification of the first suggestion displays what I need.

SELECT TOP 30 s21.SymbolId, s21.QuoteDate, s21.ClosePrice, s17.ClosePrice AS PrevPrice
FROM StockHist s21
INNER JOIN StockHist s17 ON s21.SymbolId = s17.SymbolId
WHERE s21.QuoteDate = '2013-10-21'
AND s17.QuoteDate = '2013-10-17'

Price on 10-21 Price on 10-17
3      2013-10-21 00:00:00.000      1.35      1.31
5      2013-10-21 00:00:00.000      15.11      15.22
6      2013-10-21 00:00:00.000      20.10      20.15
10      2013-10-21 00:00:00.000      3.90      4.05
14      2013-10-21 00:00:00.000      21.20      22.00

I need to use this as an UPDATE query.
The 10-21PreviousPrice column needs to be SET to the 10-17 Current price

UPDATE s21 SET s21.PrevPrice = s17.ClosePrice

What should I change?

Thanks,

LVL 11

Expert Comment

by:Angelp1ay

ID: 396894882013-12-02

One sec, I'll set it up locally to check.

Author Comment

by:Dovberman

ID: 396894892013-12-02

This works for a single selected SymbolID.

UPDATE StockHist SET ClosePricePrev =
(SELECT ClosePrice FROM StockHist WHERE QuoteDate = '2013-10-17' AND SymbolID=5)
WHERE StockHist.QuoteDate = '2013-10-21' AND SymbolID = 5

How can I update all rows for StockHist.QuoteDate = '2013-10-21' ?

Thanks,

LVL 11

Accepted Solution

by:Angelp1ay

Angelp1ay earned 2000 total points

ID: 396895052013-12-02

UPDATE s21 SET PrevPrice = s17.ClosePrice
--SELECT TOP 30 s21.StockId, s17.ClosePrice AS NewPrevPrice 
FROM StockHist s21 
INNER JOIN StockHist s17 ON s21.SymbolId = s17.SymbolId
WHERE s21.QuoteDate = '2013-10-21'
AND s17.QuoteDate = '2013-10-17'

Open in new window

The commented select helps you see the impact - I tend to write UPDATEs like this with the SELECT first and then just switch the comment.

Author Comment

by:Dovberman

ID: 396895272013-12-02

Thanks,

I will backup the table first to a table named StockHistBak.

This will take a few minutes.

Author Closing Comment

by:Dovberman

ID: 396896492013-12-02

Perfect. Thanks

LVL 11

Expert Comment

by:Angelp1ay

ID: 396896602013-12-02

You're welcome :) Have a nice day.

Featured Post

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.

Join & Write a Comment Already a member? Login.

4 Certifications Experts Exchange Can Help You Earn

Article by: Gene

Ready to get certified? Check out some courses that help you prepare for third-party exams.

How to Recover Deleted SQL Data with the Help of Best SQL Recovery Software

Article by: Bharat

In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…

Dealing With Dates: Manipulating Dates (SQL Server)

Video by: Steve

Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.