Solved

Self Join Help

Posted on 2013-12-02
Medium Priority
313 Views
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
0
Question by:Dovberman
• 5
• 4

LVL 11

Expert Comment

ID: 39689323
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'
``````
0

LVL 11

Expert Comment

ID: 39689329
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'
``````

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
``````
0

Author Comment

ID: 39689416
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,
0

LVL 11

Expert Comment

ID: 39689488
One sec, I'll set it up locally to check.
0

Author Comment

ID: 39689489
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,
0

LVL 11

Accepted Solution

Angelp1ay earned 2000 total points
ID: 39689505
``````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'
``````

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.
0

Author Comment

ID: 39689527
Thanks,

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

This will take a few minutes.
0

Author Closing Comment

ID: 39689649
Perfect. Thanks
0

LVL 11

Expert Comment

ID: 39689660
You're welcome :) Have a nice day.
0

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.