?
Solved

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

Posted on 2007-10-05
6
Medium Priority
?
233 Views
Last Modified: 2010-03-19
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.

Psuedocode:

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.?

Thanks,



0
Comment
Question by:Dovberman
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20022178
>> 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'
0
 
LVL 25

Expert Comment

by:jogos
ID: 20022727
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
0
 

Author Comment

by:Dovberman
ID: 20022746
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
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 25

Accepted Solution

by:
jogos earned 1000 total points
ID: 20022769
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.
0
 

Author Comment

by:Dovberman
ID: 20022982
"not completly clear what you want with the dates"

I know the dates.

I need the price on those two dates.

Thanks,
0
 

Author Comment

by:Dovberman
ID: 20023267
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'

Thanks,
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

755 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