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…
TransactionID CustomerID TransactionDate TransactionType TransactionAmount
------------- ---------- --------------- --------------- -----------------
1 1 2020-10-01 Credit 10.25
2 1 2020-10-02 Credit 11.50
3 1 2020-10-02 Debit -13.25
4 2 2020-10-01 Credit 14.22
5 2 2020-10-02 Debit -50.75
6 2 2020-10-04 Credit 12.85
7 2 2020-10-07 Debit -4.53
8 3 2020-10-02 Credit 12.85
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…
TransactionID CustomerID TransactionDate TransactionType TransactionAmount
------------- ---------- --------------- --------------- -----------------
4 2 2020-10-01 Credit 14.22
6 2 2020-10-04 Credit 12.85
5 2 2020-10-02 Debit -50.75
7 2 2020-10-07 Debit -4.53
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
SELECT
txn.TransactionID
, txn.CustomerID
, txn.TransactionDate
, txn.TransactionType
, txn.TransactionAmount
FROM Transactions txn
WHERE EXISTS (
SELECT
txn1.CustomerID
, SUM(txn1.TransactionAmount) AS [TransactionAmoutTotal]
FROM Transactions txn1
WHERE txn1.TransactionType = 'Credit'
AND txn1.CustomerID = txn.CustomerID
GROUP BY txn1.CustomerID
HAVING SUM(txn1.TransactionAmount) >= @ThresholdAmount
)
AND EXISTS (
SELECT
txn2.CustomerID
, SUM(txn2.TransactionAmount) AS [TransactionAmoutTotal]
FROM Transactions txn2
WHERE txn2.TransactionType = 'Debit'
AND txn2.CustomerID = txn.CustomerID
GROUP BY txn2.CustomerID
HAVING SUM(txn2.TransactionAmount) <= (@ThresholdAmount * -1)
)
ORDER BY txn.CustomerID
, txn.TransactionType
, txn.TransactionDate;
Solution #2
WITH txn_cte (TransactionID, CustomerID, TransactionDate, TransactionType, TransactionAmount, TransactionAmountTotal) AS
(
SELECT
txn.TransactionID
, txn.CustomerID
, txn.TransactionDate
, txn.TransactionType
, txn.TransactionAmount
, txn.TransactionAmountTotal
FROM (
SELECT
TransactionID
, CustomerID
, TransactionDate
, TransactionType
, TransactionAmount
, SUM(TransactionAmount) OVER (PARTITION BY CustomerID, TransactionType) AS [TransactionAmountTotal]
FROM Transactions
) txn
WHERE ABS(txn.TransactionAmountTotal) >= @ThresholdAmount
)
SELECT
txn_cte.TransactionID
, txn_cte.CustomerID
, txn_cte.TransactionDate
, txn_cte.TransactionType
, txn_cte.TransactionAmount
FROM txn_cte
WHERE EXISTS (
SELECT txn_cte1.TransactionID
FROM txn_cte txn_cte1
WHERE txn_cte1.TransactionType = 'Credit'
AND txn_cte1.CustomerID = txn_cte.CustomerID
)
AND EXISTS (
SELECT txn_cte2.TransactionID
FROM txn_cte txn_cte2
WHERE txn_cte2.TransactionType = 'Debit'
AND txn_cte2.CustomerID = txn_cte.CustomerID
)
ORDER BY txn_cte.CustomerID
, txn_cte.TransactionType
, txn_cte.TransactionDate;
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:
select transactionid, customerid, transactiondate, transactiontype, transactionamount
from (
select t.*,
sum(case when transactiontype = 'Credit' then transactionamount else 0 end)
over(partition by customerid) sum_credit,
sum(case when transactiontype = 'Debit' then transactionamount else 0 end)
over(partition by customerid) sum_debit
from transactions t
) t
where sum_credit > @ThresholdAmount and sum_debit < - @ThresholdAmount
order by customerid, transactiontype, transactiondate
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.