How do I use union within while?(SQL)

I have the following code and I want to use the union command to combine them.I tried using the union command with and without the last table(12).Thank you in advance.

DECLARE @COUNTER as int
SET @COUNTER = 0
WHILE @COUNTER < 12
  BEGIN
   SET @COUNTER = @COUNTER + 1
SELECT e.Month开发者_Go百科,e.Code,e.AVERAGE,f.AVERAGE
FROM ( SELECT     @COUNTER as Month,Clients.Clients_Code as Code, AVG(Transactions.Amount_of_indebtedness) as AVERAGE
  FROM         Account INNER JOIN
                      Clients ON Account.Account_Number = Clients.Account_Number INNER JOIN
                      Credit_Card ON Account.Account_Number = Credit_Card.Account_Number INNER JOIN
                      Transactions ON Credit_Card.Credit_Number = Transactions.Credit_Number
WHERE     (YEAR(Transactions.Date_and_time_of_transaction) = '2009') AND (MONTH(Transactions.Date_and_time_of_transaction) < @COUNTER)
GROUP BY Clients.Clients_Code)e,
  ( SELECT     @COUNTER as Month,Clients.Clients_Code as Code, AVG(Transactions.Amount_of_indebtedness) as AVERAGE
  FROM         Account INNER JOIN
                      Clients ON Account.Account_Number = Clients.Account_Number INNER JOIN
                      Credit_Card ON Account.Account_Number = Credit_Card.Account_Number INNER JOIN
                      Transactions ON Credit_Card.Credit_Number = Transactions.Credit_Number
WHERE     (YEAR(Transactions.Date_and_time_of_transaction) = '2009') AND (MONTH(Transactions.Date_and_time_of_transaction) >= @COUNTER)
GROUP BY Clients.Clients_Code)f
WHERE f.Code=e.Code AND e.Month=f.Month AND f.AVERAGE>e.AVERAGE

END

You can't.

What you could do is:

• Insert the results into a #temp table or table variable, and then select from that at the end
• Instead of using the while loop, use either a cte or a temp table to generate the numbers you need and then do a single query.