Running the same subquery multiple times

Hi,

I was wondering if this can be done...

I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?

E.g.
I have a table with 2 cols - amount, date.

SELECT total_amount, closing_amount,
FROM table1
GROUP BY month(date)

Total amount is the SUM(amount) for the month.
Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.

As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?

This is only a basic example, what I'm trying to do will invovle a lot more calcultions.

Hope someone can help,
Thanks,
Stuartuse a VIEW

:)|||--Run this in a query analyzer or sp
Declare @.Total_Amount numeric (9)
Declare @.closing_amount numeric (9)

Set @.Total_Amount = (SELECT sum(total_amount) FROM table1 GROUP BY month(date))

--Make sure you only return 1 record here
Set @.closing_Amount = (SELECT closing_amount FROM table1 Where Column_Value = my_Value GROUP BY month(date))

print @.Total_Amount
print @.closing_Amount|||db0, i don't believe you can assign a column of values to a variable|||What I did eventually was use a whole load of user-defined functions. Some of them get called multiple times. but it seems quick enough.|||and the reason you chose not to use a VIEW was... ?