Is there a better way of doing the below? The first SELECT gets all payments, so all values are positive. The next SELECT statement gets all adjustments, so all values are negative. Together, they are my derived table (JOIN) to get total customer payments (payments - adjustments).
SELECT T.PartyId, SUM(T.Amount) as 'Amount'
FROM [Transaction] T
WHERE T.PaymentTypeId = 1
GROUP BY T.PartyId
SELECT Tr.PartyId, SUM(Tr.Amount) as 'Amount'
FROM [Transaction] Tr JOIN [Transaction] T ON Tr.AdjustsTransactionId = T.TransactionId
WHERE Tr.PaymentTypeId = 9 AND
T.PaymentTypeId = 1
GROUP BY Tr.PartyId
Results might look like this:
So I would probably need to drop the above code down a level into a derived table -- call it A. Then, SELECT a SUM of Amount FROM table A, and then group by PartyId. This will be derived table B.
And then from the main select clause, select the amount from table B.
Is there a better strategy?