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