Solved

Self Join Help

Posted on 2013-12-02
9
297 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
0
Comment
Question by:Dovberman
  • 5
  • 4
9 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
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

0
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
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

0
 

Author Comment

by:Dovberman
Comment Utility
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

by:Angelp1ay
Comment Utility
One sec, I'll set it up locally to check.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Dovberman
Comment Utility
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

by:
Angelp1ay earned 500 total points
Comment Utility
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.
0
 

Author Comment

by:Dovberman
Comment Utility
Thanks,

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

This will take a few minutes.
0
 

Author Closing Comment

by:Dovberman
Comment Utility
Perfect. Thanks
0
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
You're welcome :) Have a nice day.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

772 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