将每个值从临时表传递到标量函数和求和值

I have a table valued function.Within that function i have a this code

我有一个表值函数。在该函数中我有一个这个代码

 DECLARE @totalPayment DECIMAL(18,4)
 DECLARE @totalPaymentConverted DECIMAL(18,4)
 DECLARE @fromCurrencyIdForPaymentSystemsId CHAR(3)

 DECLARE @GetTotalPaymentAmount AS TABLE 
 (
 Amount     DECIMAL(18,4),
 CurrencyId CHAR(3)
 )

INSERT INTO @GetTotalPaymentAmount
SELECT SUM(Amount),CurrencyId
FROM [dbo].[fn_DepositWithdrawReport]()
WHERE OperationTypeId = 2
GROUP BY CurrencyId

This insert return met table like this

这个插入返回符合这样的表

 A      C
-----|------
550  |USD
650  |EU
     |

I want to pass each of this values Amount and CurrencyId to the Scalar valued Function as Input parameter like this

我想将这个值Amount和CurrencyId中的每一个传递给Scalar值作为输入参数,如下所示

[dbo].[function_Valued](@totalPayment,@fromCurencyId,@toCurrencyId)

And sum that values for instance if we consider the table above

如果我们考虑上面的表格,那么总结一下这些值

[dbo].[functin_scalar](550,USD,EU)=450
[dbo].[function_scalar](650,EU,EU)=650
Get total 1100

Put simply i want calculate sum amount and i need to convert it in one currency.

简单地说我想要计算金额,我需要用一种货币兑换它。

2 个解决方案

#1

First you need to read the answer of this post to get more about the differences between Table Valued Function and Scalar function, which will help you to get more understanding on which method will work best in your situation.

首先,您需要阅读本文的答案,以了解更多关于表值函数和标量函数之间的差异,这将有助于您更好地了解哪种方法最适合您的情况。

Secondly, I'll post a simple scalar function and you need to modify it according to your needs:

其次,我将发布一个简单的标量函数,你需要根据你的需要修改它:

CREATE FUNCTION TotalPayment 
(
    @Amount         DECIMAL(18,4), 
    @FromCurrency   CHAR(3),
    @ToCurrency     CHAR(3)
)
RETURNS DECIMAL(18,4)
AS
BEGIN

    IF(@FromCurrency = 'USD' AND @ToCurrency = 'EUR')
    SET @Amount = @Amount * 0.803743

    IF(@FromCurrency = 'EUR' AND @ToCurrency = 'USD')
    SET @Amount = @Amount * 1.24418

    RETURN @Amount
END
GO

The function takes the amount with EUR as base currency, you could change that into your preferred currency. Also, you need to maintain the currency rate, as the market goes up and down in this matter, so you need to figure out a way to update the currency rate regularly . Unless your database has that data. If so, you can just select the rates from its table.

该功能以欧元作为基础货币计算金额,您可以将其更改为您的首选货币。此外,您需要维持货币汇率,因为市场在此问题上涨和下跌,因此您需要找到一种定期更新汇率的方法。除非您的数据库具有该数据。如果是这样,您只需从其表中选择费率即可。

Hint: declaring a new @param to get currency rate from another table will be useful.

提示:声明一个新的@param从另一个表中获取货币汇率将是有用的。

Then, you can use a simple SELECT statement

然后,您可以使用简单的SELECT语句

IF you need to convert all rows to EUR :

如果您需要将所有行转换为EUR:

SELECT
    Amount, 
    Currencyid, 
    dbo.TotalPayment(Amount, CurrencyId, 'EUR') AS ConvertedToEUR
FROM  GetTotalPaymentAmount

IF you want to get only the total amount (after converted):

如果您只想获得总金额(转换后):

SELECT SUM(dbo.TotalPayment(Amount, CurrencyId, 'EUR')) AS TotalinEUR
FROM  GetTotalPaymentAmount

#2

Could you do this (at the end) :-

你能做到这一点(最后): -

SELECT [dbo].[function_scalar](TheSum, CurrencyId,EU) AS GrandTotal
FROM (
SELECT SUM(Amount) AS TheSum, CurrencyId
FROM [dbo].[fn_DepositWithdrawReport]()
WHERE OperationTypeId = 2
GROUP BY CurrencyId)

#1