sql server中的nper excel函数

ubof19bj 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(285)

有人知道如何使用nperexcel函数来生成sql吗？用例是我试图在sql中找到acct的剩余项。
字段：current\u principalbalance-pv current\u interestrate/100/12-rate current\u paymentamount-付款数据中无类型数据中无fv
我试过：

Use NLS
go

declare @fv   float
declare @rate float 
declare @Pmt  float
declare @k    float 
declare @pv   float

set @fv=0
set @rate=(select (current_interest_rate/100/12) from loanacct)
set @pmt= (select amortized_payment_amount from loanacct_payment)
set @pv = (select current_principal_balance from loanacct)
set @k=1

select  Log10((-@Fv * (@Rate / @k) + @Pmt) 
        / (@Pmt + (@Rate / @k) * @Pv)) 
        / Log10(1 + @Rate) as nper

        from loanacct a, loanacct_detail b, loanacct_setup c, loan_class d, loan_group e, loanacct_payment f

where a.acctrefno = b.acctrefno
and b.acctrefno = c.acctrefno
and a.loan_class2_no = d.codenum
and e.loan_group_no = a.loan_group_no
and f.acctrefno = a.acctrefno
and e.loan_group_no = 55
and a.loan_number IN (66515,67214,65980)

但是现在我得到了一个错误：msg512，级别16，状态1，第9行子查询返回的值超过1。当子查询后跟=，！=，<，<=，>，>=或者当子查询用作表达式时。

来源：https://stackoverflow.com/questions/63107437/nper-excel-function-in-sql

2条答案

按热度按时间

um6iljoc1#

因为nper是一个forumla，所以可以在sql中实现它，因为数据库几乎总是支持公式的运算符
在查询中使用“log”时，会考虑自然对数，nper使用以10为底的log。因此，公式应修改为

select Log10((-@Fv * (@Rate / @k) + @Pmt) 
        / (@Pmt + (@Rate / @k) * @Pv)) 
        / Log10(1 + @Rate) as nper

下面是一个测试用例，它将excel nper函数中的值与sql server数据库中的值进行比较，并使用您的示例进行匹配。。
--我已经将数据类型编辑为float

declare @fv   float
declare @rate float 
declare @Pmt  float
declare @k    float 
declare @pv   float

set @fv=0
set @rate=15.99/100/12
set @pmt=-167.65
set @pv =1491.42
set @k=1
--In case the interests obtained at start of period then set @k as follows
--set @k = 1 + @rate

select Log10((-@Fv * (@Rate / @k) + @Pmt) 
        / (@Pmt + (@Rate / @k) * @Pv)) 
        / Log10(1 + @Rate) as nper

+------------------+
|       nper       |
+------------------+
| 9.53201056406188 |
+------------------+

excel=nper（15.99/100/12，-167.651491.42,0）

这里有一个dbfiddle链接https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=2f470cee443e10647f83d6e640129d51

赞(0）回复(0）举报 2021-07-29

ql3eal8s2#

对于这个用例，这是我的解决方案，它非常适合在excel中使用nper函数：

declare @fv   float
declare @k    float 

set @fv=0
set @k=1

select CEILING(Log10((-@Fv * ((current_interest_rate/100/12) / @k) + -amortized_payment_amount) 
        / (-amortized_payment_amount + ((current_interest_rate/100/12) / @k) * current_principal_balance)) 
        / Log10(1 + (current_interest_rate/100/12))) as nper

赞(0）回复(0）举报 2021-07-29

我来回答

sql server中的nper excel函数

2条答案

相关问题

热门标签

最新问答