Solved

Self Join Help

Posted on 2013-12-02
9
303 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

751 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