Skip to content
Advertisement

Better way to find records meeting two different aggregate criteria?

I need to find customers that have a sum of credit transactions greater than or equal to a threshold amount and a sum of debit transactions less than or equal to the inverse of that same threshold amount. I also want to return the transactions whose aggregate transaction amount satisfies the credit and debit thresholds. I have come up with two solutions, but I think I might be making things more complicated than necessary.

Here is some simplified sample data…

With a threshold amount of $20 CustomerID 2 is the only customer that meets that threshold for both credits and debits. So here is my expected result…

I have come up with two solutions. I should mention that due to the software in which I ultimately need to embed this query I a cannot use temp tables or table variables.

Solution #1

Solution #2

Here is the dbfiddle of my sample data inserts and two solutions. Is there a better way to do this given I cannot use temp tables or table variables?

Advertisement

Answer

You can use window functions:

Within the subquery, the conditional sums compute the total debit and credit of each customer. You can then use this information for filtering in the outer query.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement