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

Viewed 479

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
2 Answers
Related