SQL Server 2008 Stored Procedure multiple SELECTS returned as one set

Question

I'd like to write a SQL Server 2008 stored procedure which returns in one record:

• Max Stock price for the year,
• Min Stock price for the year,
• Max Stock price for the month,
• Min Stock price for the month

Reviewing previous posts I've tried select#1 UNION ALL select#2 but this returns two result rows(regardless of AS) of two columns rather than one row of four columns. (I'm a noob and thus not completely sure this is a problem that can't be parsed by the webserver or webpage -- my coder has said one row is preferable -- so advice in that regard is great too).

Temporary tables and a new 2012 feature also seem possible but is there an easier way that I am missing?

Answer 1

You have not provided table definitions but....

SELECT 
MIN(StockPrice) MinYr, 
MAX(StockPrice) MaxYr, 
MIN(
    CASE 
       WHEN MONTH(ADate) = MONTH(GETDATE()) 
       THEN StockPrice 
       ELSE NULL 
    END
) MinMth, 
MAX(
    CASE 
       WHEN MONTH(ADate) = MONTH(GETDATE()) 
       THEN StockPrice 
       ELSE NULL 
    END
) MaxMth
FROM Table
WHERE YEAR(ADate) = Year(GETDATE())

This is not necessarily the best SQL for performance (as there are functions around columns). It is the simplest code though.