Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-04 : 09:13:20
|
| Jake writes "I would like to know if it is possible to run a query on the resultset of another query. Specifically I have a table, stock_data, with four columns: stockName (varchar), quarter (int), year (int), price (float). I want to be able to compare stocks but limit the date range to those quarters where both stocks have data. I am assuming there will be no holes in the middle of the data so if a stock has data for q2 of 2000 and q4 of 2000, it will also have data for q3 of 2000. However, stock 'A' may have data for q2 1999 - q3 2000 and stock 'B' may have data for q1 2000 - q2 2001. Right now my query looks something like: select stockName, max([year]*4 + [quarter]) mx, min([year]*4 + [quarter]) mn from stock_data where stockName in ('A', 'B') group by stockName. This returns:stockName.....mx.....mnA.............8003...7998B.............8006...8001I would like to be able to simply return the maximum of the 'mn' column (8001), and the minimum of the 'mx' column (8003) which will give me the ranges for which the data overlaps for those stocks. Is there a way to do this entirely in SQL?" |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2001-12-04 : 09:52:57
|
Sure. Look in the BOL under derived tables. The only thing you need to remember is that a derived table must be aliased.In your case:select min(mx), max(mn) from ( select stockName, max([year]*4 + [quarter]) mx, min([year]*4 + [quarter]) mn from stock_data where stockName in ('A', 'B') group by stockName) a |
 |
|
|
|
|
|
|
|
Querying the resultset of another query