How do I efficiently create logical subsets of data in a many-to-many mapping table?
===

I have a many-to-many relationship between invoices and credit card transactions, which I'm trying to map sums of together.  My desired output is the following:

Given the following transactions/invoices

    Table: TransactionInvoiceMap
    TransactionID  InvoiceID
    1              1
    2              2
    3              2
    3              3

    Table: Transactions
    TransactionID  Amount
    1              $100
    2              $75
    3              $75

    Table: Invoices
    InvoiceID  Amount
    1          $100
    2          $100
    3          $50

my desired output is

    TransactionID  TotalAsscTransactions TotalAsscInvoiced
    1              $100                  $100
    2              $150                  $150
    3              $150                  $150

Note that invoices 2 and 3 and transactions 2 and 3 are part of a logical group.  The two transactions for the two invoices as a group.

Here's a solution (simplified, names changed) that apparently works, but is very slow.  I'm having a hard time figuring out how to optimize this, but I think it would involve eliminating the subqueries into TransactionInvoiceGrouping.  Feel free to suggest something radically different.

	with TransactionInvoiceGrouping as (
		select 
			-- Need an identifier for each logical group of transactions/invoices, use
			-- one of the transaction ids for this.
			m.TransactionID,
			m.InvoiceID,
			min(m.TransactionID) over (partition by m.InvoiceID) as GroupingID
		from TransactionInvoiceMap m
	)
	select distinct
		g.TransactionID,
		istat.InvoiceSum as TotalAsscInvoiced,
		tstat.TransactionSum as TotalAsscTransactions
	from TransactionInvoiceGrouping g
		cross apply (
			select sum(ii.Amount) as InvoiceSum
			from (select distinct InvoiceID, GroupingID from TransactionInvoiceGrouping) ig
				inner join Invoices ii on ig.InvoiceID = ii.InvoiceID
			where ig.GroupingID = g.GroupingID
		) as istat
		cross apply (
			select sum(it.Amount) as TransactionSum
			from (select distinct TransactionID, GroupingID from TransactionInvoiceGrouping) ig
				left join Transactions it on ig.TransactionID = it.TransactionID
			where ig.GroupingID = g.GroupingID
			having sum(it.Amount) > 0
		) as tstat
