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?

`UNION ALL` returns only 1 result set, not two. It could return 2 rows however. Sounds like you want a single row with 4 columns? — Lock, Aug 19 '15 at 04:18
Can you please show the SQL with 'union all'? I'd expect such one to work. — jboi, Aug 19 '15 at 04:18

Nick.Mc · Answer 1 · 2015-08-19T05:01:14.533

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.

SQL Server 2008 Stored Procedure multiple SELECTS returned as one set

1 Answers1