asked on

Self Join Help

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

Angelp1ay

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

Angelp1ay

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

Dovberman

ASKER

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,

Angelp1ay

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

Dovberman

ASKER

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,

ASKER CERTIFIED SOLUTION

Angelp1ay

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dovberman

ASKER

Thanks,

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

This will take a few minutes.

Dovberman

ASKER

Perfect. Thanks

Angelp1ay

You're welcome :) Have a nice day.