Click here to Skip to main content
15,851,242 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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
Posted
Updated 4-Aug-18 6:58am
v5
Comments
Mehdi Gholam 4-Aug-18 2:23am    
Start by defining your problem clearly (your post is hard to follow for anyone but you, and you seem lost yourself).
Member 13250972 4-Aug-18 8:59am    
Thank you, but I feel I have described the problem quite clearly; and if I weren't lost on the aspect I am trying to solve, I would not be posting a question on this forum. However, I will attempt to break it down.

Users may make refundable deposits into their user_cash_wallet (user_cash).
My website may make non-refundable deposits into their user_cash_wallet (site_cash).
Users may expend cash from their user_cash_wallet (expended_cash).
Cash expended from user_cash_wallet must be deducted from the site_cash funds before user_cash funds first.

The balance reflected is always user_cash_deposited + site_cash_deposited - expended_cash.

If the user decides they want to withdraw cash they deposited, I need to be able to calculate how much user_cash remains by determining how much site_cash has been expended. If any amount of site_cash remains unused, then they should be able to withdraw the entire amount of deposited user_cash; otherwise, they should be able to withdraw the cash balance since all site_cash has been used.

Please let me know if the problem is more clear. I thought I might need to consider when the site_cash was deposited, but I feel like my solution is correct - I would just like to get a sanity check to make sure I am not missing anything.
Member 13250972 4-Aug-18 10:25am    
And by the way, my solution does not work. As I suspected, I need to be able to calculate based on the dates when the site_cash was deposited and expended_cash occurring afterwards.

Example:
$2.50 site_cash is deposited by the site.
The user expends $0.50 leaving a balance of $2.00 (unused site_cash). Of which $0.00 is refundable.
The user deposits $2.50, leaving a balance of $4.50. Of which $2.50 is refundable; leaving $2.00 unused site_cash.
The user expends another $3.00, leaving a balance of $1.50. Of which $1.50 is refundable since the $2.00 unused site_cash is used up first. Everything is correct up to this point.

Here's where it screws up. If the site deposits another $2.50 site_cash leaving a balance of $4.00, my script is indicating that the user may now withdraw $2.50 because the math is drawing the $1.00 that previously came out of the user_cash deposit from the newly deposited site_cash when only future expenditures should come out of site_cash funds.

If I have only made things more unclear with this example, then I guess I am on my own.

1 solution

Sorry Code Project, but you guys are 0 for 3 in helping me. Maybe my problems are too complex? I don't ask for help often, but when I do... you don't.

This is ultimately what I had to do to determine the unused site_cash to calculate how much of the Cash Balance a user could withdraw.

It sums the amount of expended_cash after each deposit of site_cash (claimed_dz_cash).
I may still need to do some tweaking, but I think I got it from here.

SELECT 
	SUM(unused_dz_cash) as unused_dz_cash
FROM
(
	SELECT 
		SUM(cw.amount) as expended_cash, 
		cw_dz.amount as claimed_dz_cash,
		CASE WHEN cw_dz.amount + SUM(cw.amount) > 0 THEN cw_dz.amount + SUM(cw.amount) 
			ELSE 0 END as unused_dz_cash
	FROM user_wallet_cash cw_dz
		JOIN user_wallet_cash cw WITH (NOLOCK) ON cw_dz.create_date <= cw.create_date
	WHERE
		cw_dz.transaction_type_id = 3 
		AND cw_dz.user_id = @user_id 
		AND cw.transaction_type_id NOT IN (2,3)
		AND cw.user_id = @user_id 
	GROUP BY 
		cw_dz.user_wallet_cash_id,cw_dz.amount
)t
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900