How to optimize SQL of select x divide by subquery with sum(y) with different table?

Question

I have this query in T-SQL 2008:

SELECT a.Amount / (
    SELECT SUM(b.Amount) FROM Revenue b 
    WHERE YEAR(b.RevenueDate) = YEAR(a.ExpenseDate)
    AND MONTH(b.RevenueDate) = MONTH(a.ExpenseDate)
    AND b.HotelKey = a.HotelKey
)
FROM Expense a

The problem is it takes too long to finish the query. I think it's caused by the subquery "SELECT SUM(b.Amount) FROM Revenue b..." which is executed for each row in table Expense.

How to optimize that kind of query? Is there any better alternative for the query?

EDIT: I'm sorry I forget the "AND b.HotelKey = a.HotelKey" clause in the subquery. The above original query has been updated.

@damien:

Here is your query added with HotelKey join:

SELECT
    a.Amount / b.Amount
FROM
    Expense a
    inner join
    (SELECT
        HotelKey,
        DATEADD(month,DATEDIFF(month,0,RevenueDate),0) as MonthStart,
        DATEADD(month,1+DATEDIFF(month,0,RevenueDate),0) as MonthEnd,
        SUM(Amount) as Amount
     FROM
        Revenue
     GROUP BY
        HotelKey,
        DATEADD(month,DATEDIFF(month,0,RevenueDate),0),
        DATEADD(month,1+DATEDIFF(month,0,RevenueDate),0)
    ) b 
    ON
        a.ExpenseDate >= b.MonthStart and
        a.ExpenseDate < b.MonthEnd 
        and a.HotelKey = b.HotelKey

Answer 1

Try to change the where clause in your inner query to this:

where b.RevenueDate >= dateadd(month, datediff(month, 0, a.ExpenseDate), 0) and
      b.RevenueDate < dateadd(month, 1+datediff(month, 0, a.ExpenseDate), 0)

It will give the query a chance to use an index on Revenue.RevenueDate if you have one.

Answer 2

If you're using a lot of the rows in Revenue to satisfy this query, you might do better to do a single subquery that computes all of the totals. (Also, using Mikael's suggestion for allowing some indexing to occur):

SELECT
    a.Amount / b.Amount
FROM
    Expense a
        inner join
    (SELECT
         DATEADD(month,DATEDIFF(month,0,RevenueDate),0) as MonthStart,
         DATEADD(month,1+DATEDIFF(month,0,RevenueDate),0) as MonthEnd
         SUM(Amount) as Amount
     FROM
         Revenue
     GROUP BY
         DATEADD(month,DATEDIFF(month,0,RevenueDate),0),
         DATEADD(month,1+DATEDIFF(month,0,RevenueDate),0)
    ) b 
         ON
              a.ExpenseDate >= b.MonthStart and
              a.ExpenseDate < b.MonthEnd