查找未退还金额的未使用资金 [英] Find unused funds of nonrefundable amounts

问题描述

下午好，



（我认为我在我已经尝试过的部分中的解决方案实际上是正确的，但是如果有人能确认这是真棒的话）< br $> b $ b

我正在尝试确定如何计算可退还的存款可能存在的不可退还的存款中剩余的未使用资金。交易应被视为首先从不可退还的金额中提取，以确定可以退还已存入的可退还金额和不可退还金额之间的余额。



在下表中，< b> transaction_type_id = 3 不可退款， transaction_type_id = 2 可退款。在这种情况下，总存款为10.00美元的可退还和不可退还的资金。已花费0.34美元，但应首先从不可退还的押金中扣除。



现在我明白我们可以看看这个例子并看到整个5.00美元可以退还，因为并非所有不可退还的资金都已用完，且没有可退还的资金。但为了简洁起见，我不得不删除很多行。



在SQL中，我如何确定已经使用了多少不可退还的资金？即如果支出总额为6.50美元，我需要能够证明只有3.50美元可以退款。



简单地用数学方法做这件事是行不通的，因为可退还的存款总额可能远远超过不可退还的存款总额;所以我不能只说不可退还的存款 - 总支出。日期将在这里发挥作用，因为当存入不可退还的金额时，即使在余额中有可退还的资金，支出也会从第一次开始。



预先感谢您的帮助



金额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 18年8月2日

-0.01 5 8/2/18

2.5 3 8/2/18



什么我试过了：



我得到了不可退还的存款的临时表 transaction_type_id 3然后试图查看哪些交易来自这些资金。但说实话，我很丢失。



我不是在找人为我写脚本。一个简单的语言解释我应该如何处理这个问题会让我感觉到这一点。



这是我做的一次尝试，但我不确定它是否捕获我在尝试什么。

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

推荐答案

10.00可退款且不可退还的资金。

10.00 of refundable and non-refundable funds.

0.34已经用完，但应该先从不可退还的押金中扣除。



现在我明白我们可以看看这个例子，看看整个

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可以退还，因为并非所有不可退还的资金都已用完，且没有可退还的资金。但为了简洁起见，我不得不删除很多行。



在SQL中，我如何确定已经使用了多少不可退还的资金？即如果支出总额是

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

这篇关于查找未退还金额的未使用资金的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！