SQL syntax that returns columns from 2 rows of the same table on the same result row. SQL Server 2005

Need a SQL statement that returns columns from 2 rows of the same table on the same result row.

I have a Primary key, QuoteDate and Cl.osePrice column in a SQL Server 2005 table.

I need a SQL statement that returns the QuoteDate and ClosePrice for two given dates.


SELECT (QuoteDate on '09/15/2007') As PreviousDate, (ClosePrice on '09/15/2007') As PreviousPrice,
(QuoteDate on '09/30/2007') As RecentDate, (ClosePrice on '09/302007') As RecentPrice
WHERE PrimnaryKey = Value

What are the expressions for PreviousDate, etc.?


Who is Participating?
jogosConnect With a Mentor Commented:
I gave a general example because it's not completly clear what you want with the dates.

Attention! When making it a left join (previous doesn't have to exists) you must be carefull what to put in a where-clause.
Jinesh KamdarCommented:
>> QuoteDate on '09/15/2007'
>> QuoteDate on '09/30/2007'

Which date-column do you wish to compare for '09/15/2007' and '09/30/2007'
ther is no problem with a Join to the same table

select A.Date, b.date as previousdate
from MyPrice as A
inner join MyPrice as B
on (A.product= B.productID  
   and B.date = (select  max(c.date) from myprice C
                         where c.productID = a.productId and c.date < a.date)

The inner join will only give you result when there is a previous price
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

DovbermanAuthor Commented:
I wish to compare the price on two different dates for the same primary key(SyhmbolID).

I tried this:

SELECT SymbolID, MarketID,
(Select ClosePrice WHERE QuoteDate='09/13/2007' AND SymbolID=24986 ) As PreviousPrice,
(Select ClosePrice WHERE QuoteDate='09/26/2007' AND SymbolID=24986 ) As RecentPrice
FROM DayQuote WHERE SymbolID=24986

32 rows returned. PreviousPrice and RecentPrice are returned as NULL
DovbermanAuthor Commented:
"not completly clear what you want with the dates"

I know the dates.

I need the price on those two dates.

DovbermanAuthor Commented:
I should have made it clear that I needed the price on 2 different dates.

Ths self join worked.

select a.ClosePrice As RecentPrice, b.ClosePrice as PreviousPrice,
((a.ClosePrice-b.ClosePrice)/b.ClosePrice) * 100 As PctDiff  
from DayQuote as a
inner join DayQuote as b
on (a.SymbolID= b.SymbolID)
WHERE b.SymbolID=24986 AND a.QuoteDate='09/26/2007'
AND b.QuoteDate='09/13/2007'

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.