SQL Query for Price Data of Different Stocks from 1 table with same dates

10 pts.
Tags:
SQL Express
SQL Express 2005
SQL Query
SQL Server
I am having 1 table with date, stock and price data for all stocks. I want a sql query which will display the date, stockname1, stockname2, stockprice1 and stockprice2 for the 2 stocks I mention and the date range I mention and date should be common to both stocks. If stock1 has a price on lets say 1-Jan-2010 but stock 2 doesnot have a price on that date, the record should not come, only dates where both stock have a price should be displayed. Thanks

Software/Hardware used:
SQL Express 2005
ASKED: June 17, 2010  7:58 AM
UPDATED: June 17, 2010  5:10 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

I’m not sure I understand correctly, but you could try something similar to this:

<pre>SELECT s1.date,s1.stock,s2.stock,s1.price,s2.price
FROM your_table s1 JOIN your_table s2
ON s1.date = s2.date AND s1.stock != s2.stock
WHERE s1.stock = <something>
AND s2.stock = <something>
AND s1.date BETWEEN <something> AND <something>;</pre>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following