How to combine two queries in a stored procedure to get the result in one line

Question

I have a stored procedure in which I joined two queries. my requests

alter PROCEDURE test @SDate datetime, @EDate datetime 

how

 -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here (Select count(quoteid) as TotalQuote, sum(totalamount) as QuoteAmount from dbo.QuoteBase where CreatedOn BETWEEN @SDate AND @EDate) union All (select count(salesorderid)as TotalOrders, sum(totalamount) as OrderAmount from dbo.SalesOrderBase Where CreatedOn BETWEEN @SDate AND @EDate) 

and I got the result in a column

 Total Quote Quote Amount 17 700 118 5000 

but I need a result like

 Total Quote Quote Amount Total Orders Order Amount 17 700 118 5000 

If anyone has an idea, please share with me.

Answer 1

Try this option -

 ALTER PROCEDURE dbo.usp_test @SDate DATETIME , @EDate DATETIME AS BEGIN SET NOCOUNT ON; SELECT t.TotalQuote , t.QuoteAmount , t2.TotalOrders , t2.OrderAmount FROM (SELECT a = 1) a CROSS JOIN ( SELECT TotalQuote = COUNT(quoteid) , QuoteAmount = SUM(totalamount) FROM dbo.QuoteBase WHERE CreatedOn BETWEEN @SDate AND @EDate ) t CROSS JOIN ( SELECT TotalOrders = COUNT(salesorderid) , OrderAmount = SUM(totalamount) FROM dbo.SalesOrderBase WHERE CreatedOn BETWEEN @SDate AND @EDate ) t2 END 

Update:

 SELECT t.TotalQuote , t.QuoteAmount , t2.TotalOrders , t2.OrderAmount FROM ( SELECT TotalQuote = COUNT(quoteid) , QuoteAmount = SUM(totalamount) FROM dbo.QuoteBase WHERE CreatedOn BETWEEN @SDate AND @EDate ) t FULL OUTER JOIN ( SELECT TotalOrders = COUNT(salesorderid) , OrderAmount = SUM(totalamount) FROM dbo.SalesOrderBase WHERE CreatedOn BETWEEN @SDate AND @EDate ) t2 ON 1 = 1 

Answer 2

You can use cross join instead of union :

 select q1.TotalQuote , q1.QuoteAmount , q2.TotalOrders , q2.OrderAmount from ( ... first query ... ) q1 cross join ( ... second query ... ) q2