Solved

Self Join Help

Posted on 2013-12-02
9
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
0
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
  • 5
  • 4
9 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
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'

Open in new window

0
 
LVL 11

Expert Comment

by:Angelp1ay
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'

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
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

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

Author Comment

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

by:
Angelp1ay earned 500 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'

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
ID: 39689527
Thanks,

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

This will take a few minutes.
0
 

Author Closing Comment

by:Dovberman
ID: 39689649
Perfect. Thanks
0
 
LVL 11

Expert Comment

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

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

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…
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

623 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