I have a many-to-many relationship between invoices and credit card transactions, which I'm trying to map sums of together. The best way to think of the problem is to imagine TransactionInvoiceMap as a bipartite graph. For each connected subgraph, find the total of all invoices and the total of all transactions within that subgraph. In my query, I want to return the values computed for each of these subgraphs along with the transaction ids they're associated with. Totals for related transactions should be identical.
More explicitly, 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.
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