Okay. I think I figured out where the problem is. I also realized that I didn't need one of the clauses in one of the CASE statements, so I've removed that. It probably won't make a significant difference in the speed, but it will make it ever so slightly easier to understand.
;
WITH totals AS (
SELECT *, ABS(Amount) AS Amt, SUM(ABS(Amount)) OVER(PARTITION BY Customer, TransactionType ORDER BY Date ROWS UNBOUNDED PRECEDING ) AS tot
FROM #t1
)
, balances AS (
SELECT *, MIN(CASE WHEN TransactionType = 'Debit' THEN Date END) OVER(PARTITION BY Customer ORDER BY tot, TransactionType ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS PaidDate,
LAG(TransactionType) OVER(PARTITION BY Customer ORDER BY tot, TransactionType) AS PrevTransactionType,
MAX(CASE WHEN TransactionType = 'Debit' THEN Date END) OVER(PARTITION BY Customer ORDER BY tot, TransactionType ROWS UNBOUNDED PRECEDING) AS LastPaid,
LAG(tot) OVER(PARTITION BY Customer ORDER BY tot, TransactionType) AS PrevTot
FROM totals
)
SELECT TransactionID, Customer,
CASE
WHEN PaidDate IS NOT NULL THEN 0
ELSE tot - PrevTot
END AS RemainingBalance,
CASE
WHEN PaidDate IS NOT NULL THEN PaidDate
WHEN PrevTransactionType = 'Debit' THEN LastPaid
END AS LastRedeemedDate
FROM balances
WHERE TransactionType = 'Credit'
ORDER BY Customer, Date
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA