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.
Solved
Self Join Help
Posted on 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
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
[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
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'
I figure you want to compare to check they are the same value...
Comparison as a column:
Filter showing mismatches:
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'
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
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,
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,
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,
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,
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'
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.
Featured Post
ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses
Course of the Month6 days, left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.