Solved

Self Join Help

Posted on 2013-12-02

1 solution

304 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

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

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

If you are experiencing a similar issue, please ask a related question

How to dynamically return a table structure using a Stored Procedure

Article by: Ryan Chong

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…

Installing SQL Server vNext on Linux: Part 2

Article by: Yashwant

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.