Issue combining 2 GROUP BY queries into one

[Lynda]

I have inherited a stored procedure that runs a GROUP BY query, places it into a temp table, then combines columns from the temp table into another GROUP BY query. If possible I'd like to do away with the temp table and run it using one query.

The first query is:

SELECT M.[CO No], DateName(month, S.[Calendar Mnth Key]) AS Month_Name, LEFT(S.[Calendar Mnth Key], 4) AS Year, S.[Calendar Mnth Key]) AS [Date], [Company Name], SUM(S.Amt) AS [2010 Sales], SUM(S.[Txn Cnt]) AS [2010 Sales Trans], [2010 Avg Sales Ticket] =
   CASE SUM(S.[Txn Cnt])
       WHEN 0 THEN 0
   ELSE SUM(S.[Amt)/SUM(S.[Txn Cnt]) END,
FROM Merch AS M
   INNER JOIN Sales AS S
	ON M.[Cust Key] = S.[Cust Key]
WHERE M.[CO No] = 1234
   AND (S.[Calendar Mnth Key] >= 201001
   AND S.[Calendar Mnth Key] <= 201012) 
   AND S.[Mop Key] IN (16,24) 
GROUP BY M.[CO No], DateName(month, S.[Calendar Mnth Key]), LEFT(S.[Calendar Mnth Key], 4), S.[Calendar Mnth Key]), [Company Name]
ORDER BY S.[Calendar Mnth Key]

The second query, without the fields from the first query, would be the exact same as the first, only dealing with Returns:

SELECT M.[CO No], DateName(month, R.[Calendar Mnth Key]) AS Month_Name, LEFT(R.[Calendar Mnth Key], 4) AS Year, R.[Calendar Mnth Key]) AS [Date], [Company Name], SUM(R.Amt) AS [2010 Returns], SUM(R.[Txn Cnt]) AS [2010 Return Trans], [2010 Avg Return Ticket] =
   CASE SUM(R.[Txn Cnt])
       WHEN 0 THEN 0
   ELSE SUM(R.[Amt)/SUM(R.[Txn Cnt]) END,
FROM Merch AS M
   INNER JOIN Returns AS R
	ON M.[Cust Key] = R.[Cust Key]
WHERE M.[CO No] = 1234
   AND (R.[Calendar Mnth Key] >= 201001
   AND R.[Calendar Mnth Key] <= 201012) 
   AND R.[Mop Key] IN (16,24) 
GROUP BY M.[CO No], DateName(month, R.[Calendar Mnth Key]), LEFT(R.[Calendar Mnth Key], 4), R.[Calendar Mnth Key]), [Company Name]
ORDER BY R.[Calendar Mnth Key]

I need to combine them into one query to return the results below:

CO No	Month_Name	Year	Date	Company Name	2010 Sales	2010 Trans	2010 Avg Ticket	2010 Returns	2010 Trans	2010 Avg Ticket
1234	January	2010	1/1/2010	Bob's Bobbers	 $4,485,980.95 	9322	 $          481.23 	 $(14,540.52)	56	 $         (259.65)
1234	February	2010	2/1/2010	Bob's Bobbers	 $3,743,675.54 	8120	 $          461.04 	 $(21,844.87)	74	 $         (295.20)
1234	March	2010	3/1/2010	Bob's Bobbers	 $4,068,638.11 	9028	 $          450.67 	 $(26,547.92)	80	 $         (331.85)
1234	April	2010	4/1/2010	Bob's Bobbers	 $4,474,000.98 	9709	 $          460.81 	 $(35,441.54)	97	 $         (365.38)
1234	May	2010	5/1/2010	Bob's Bobbers	 $5,041,425.10 	10817	 $          466.06 	 $(35,551.93)	110	 $         (323.20)
1234	June	2010	6/1/2010	Bob's Bobbers	 $5,832,407.31 	12703	 $          459.14 	 $(36,107.16)	128	 $         (282.09)

Thanks for any help you can give!

[ck9663]

Try something like...

SELECT 'sales' as source, alltheothercolumns
from YourFirstQuery
UNION ALL
SELECT 'returns' as source, alltheothercolumns
from YourSecondQuery

The derived "source" column will identify which record came from which query.

Good Luck!!!

~~ CK