Find unused funds of nonrefundable amounts

See more:

SQL

Good afternoon,

(I think my solution in the What I have Tried section is correct actually, but if someone could confirm that'd be awesome)

I'm trying to determine how to calculate how much unused funds remain from non-refundable deposits where refundable deposits may also exist. Transactions should be considered as drawn from the nonrefundable amounts first to determine how much of the balance between deposited refundable and nonrefundable amounts may be withdrawn.

In the table below, transaction_type_id = 3 are nonrefundable, and transaction_type_id = 2 are refundable. In this case the total deposit was $10.00 of refundable and non-refundable funds. $0.34 has been expended, but it should be deducted from the non-refundable deposits first.

Now I understand that we can just look at this example and see that the entire $5.00 can be refunded since not all of the non-refundable funds have been expended and none of the refundable funds have. But I had to remove a lot of the rows for the sake of brevity.

In SQL, how could I determine how much of the non-refundable funds have been used? i.e. if the total amount of expenditures had been $6.50, I need to be able to show that only $3.50 can be refunded.

Simply doing it mathematically won't work because there may be far more expended in than the total amount of non-refundable deposits; so I can't just say non-refundable deposits - total expenditures. Dates are going to play a part in this since when a non-refundable amount is deposited, expenditures come out of that first, even if there are refundable funds in the balance.

Thanks in advance for any help

amount transaction_type_id create_date
2.5 3 7/20/18
-0.01 5 7/20/18
-0.01 5 7/22/18
-0.01 5 7/22/18
-0.02 4 7/22/18
-0.02 4 7/22/18
-0.02 4 7/22/18
-0.02 4 7/22/18
-0.01 5 7/22/18
-0.01 5 7/22/18
-0.01 5 7/24/18
-0.01 5 7/24/18
2.5 2 8/2/18
2.5 2 8/2/18
-0.01 5 8/2/18
-0.01 5 8/2/18
-0.01 5 8/2/18
-0.01 5 8/2/18
-0.02 4 8/2/18
-0.01 5 8/2/18
-0.02 4 8/2/18
-0.02 4 8/2/18
-0.02 4 8/2/18
-0.02 4 8/2/18
-0.01 5 8/2/18
-0.01 5 8/2/18
-0.01 5 8/2/18
-0.01 5 8/2/18
2.5 3 8/2/18

What I have tried:

I got as far as making a temp table of the non-refundable deposits transaction_type_id 3 and then attempted to see which transactions would have come out of those funds. but I got pretty lost to be honest.

I'm not looking for someone to write my scripts for me. A plain language explanation of how I should approach this would get me just as far.

Here was an attempt I made, but I'm not really certain that it captures what I am attempting.

DECLARE @user_id as INT
SET @user_id = 8004

SELECT 
	SUM(DZCash) as DZCashClaimed,
	CASE WHEN SUM(DZCash) + SUM(CashExpended) > 0 THEN SUM(DZCash) + SUM(CashExpended) ELSE 0 END as UnusedDZCash,
	SUM(CashDeposited) as CashDeposited,
	SUM(CashExpended) as CashExpended,
	SUM(CashBalance) as CashBalance,
	SUM(CashBalance) - CASE WHEN SUM(DZCash) + SUM(CashExpended) > 0 THEN SUM(DZCash) + SUM(CashExpended) ELSE 0 END as ElligibleAMountForWithdraw
FROM 
	(
		SELECT 
		SUM(amount) as DZCash,
		0 as CashDeposited,
		0 as CashExpended,
		0 CashBalance
		FROM user_wallet_cash 
		WHERE user_id = @user_id
		AND amount > 0 AND transaction_type_id = 3

		UNION

		SELECT 
		0 as DZCash,
		SUM(amount) as CashDeposited,
		0 as CashExpended,
		0 CashBalance
		FROM user_wallet_cash 
		WHERE user_id = @user_id
		AND amount > 0 AND transaction_type_id = 2

		UNION

		SELECT 
		0 as DZCash,
		0 as CashDeposited,
		SUM(amount) as CashExpended,
		0 CashBalance
		FROM user_wallet_cash 
		WHERE user_id = @user_id
		AND amount < 0 AND transaction_type_id IN (4,5)

		UNION

		SELECT 
		0 as DZCash,
		0 as CashDeposited,
		0 as CashExpended,
		SUM(amount) as CashBalance
		FROM user_wallet_cash 
		WHERE user_id = @user_id
	)t