首页 > 编程入门 文章详情

SQL TVM或年金函数PV，FV，NPER，RATE，PMT(SQL TVM or Annuity Functions PV,FV,NPER,RATE,PMT)

编程入门 行业动态 更新时间:2024-10-28 06:22:45
SQL TVM或年金函数PV，FV，NPER，RATE，PMT(SQL TVM or Annuity Functions PV,FV,NPER,RATE,PMT)

我试图得到一些UDF，作为一个好的程序员更喜欢CRTL + C ， CRTL + V方法。 这和我不是100％的数学要求。 基本上，我希望模仿excel中的函数： NPER() ， RATE() ， PV() ， FV() ， PMT()由于某种原因这些令人惊讶地难以找到，但我不相信有人没有他们四处闲逛。 我正在使用SQL SERVER 2008，所以如果我有企业或BI，他们会被添加，这就是为什么没有人要求他们。 下面是我发现的一个似乎适用于PMT()

CREATE FUNCTION DBO.fPMT ( -- DATETYPE FOR @RATE CHANGED TO FLOAT @RATE FLOAT, @PERIODS SMALLINT, @PRINCIPAL NUMERIC(20,2) ) RETURNS NUMERIC (38,9) AS BEGIN DECLARE @PMT NUMERIC (38,9) DECLARE @WK_PERIODS FLOAT, @WK_PRINCIPAL FLOAT, @WK_ONE FLOAT, @WK_POWER FLOAT -- CONVERT DATA FOR CALCULATION SELECT @WK_PERIODS = @PERIODS, @WK_PRINCIPAL = @PRINCIPAL, @WK_ONE = 1 IF (POWER(@WK_ONE+@RATE,@WK_PERIODS)-@WK_ONE) = 0 SET @PMT = 0 ELSE BEGIN SELECT @PMT = ROUND( -- DO ALL MULTIPLICATION BEFORE DIVISION ( @WK_PRINCIPAL * (@RATE*POWER(@WK_ONE+@RATE,@WK_PERIODS))) / (POWER(@WK_ONE+@RATE,@WK_PERIODS)-@WK_ONE) ,9) END RETURN @PMT END

I am trying to get a few UDF together and as a good programer prefer the CRTL+C, CRTL+V method. That and I am not 100% on the math required. Basically, I am looking to emulate the functions from excel: NPER(),RATE(),PV(),FV(),PMT() for some reason these have been surprisingly hard to find, but I cannot believe someone does not have them laying around. I am using SQL SERVER 2008 so maybe if I had enterprise or BI they would be added and that is why no one is has asked for them. Below is one I have found that seems to be working well for PMT()

CREATE FUNCTION DBO.fPMT ( -- DATETYPE FOR @RATE CHANGED TO FLOAT @RATE FLOAT, @PERIODS SMALLINT, @PRINCIPAL NUMERIC(20,2) ) RETURNS NUMERIC (38,9) AS BEGIN DECLARE @PMT NUMERIC (38,9) DECLARE @WK_PERIODS FLOAT, @WK_PRINCIPAL FLOAT, @WK_ONE FLOAT, @WK_POWER FLOAT -- CONVERT DATA FOR CALCULATION SELECT @WK_PERIODS = @PERIODS, @WK_PRINCIPAL = @PRINCIPAL, @WK_ONE = 1 IF (POWER(@WK_ONE+@RATE,@WK_PERIODS)-@WK_ONE) = 0 SET @PMT = 0 ELSE BEGIN SELECT @PMT = ROUND( -- DO ALL MULTIPLICATION BEFORE DIVISION ( @WK_PRINCIPAL * (@RATE*POWER(@WK_ONE+@RATE,@WK_PERIODS))) / (POWER(@WK_ONE+@RATE,@WK_PERIODS)-@WK_ONE) ,9) END RETURN @PMT END

最满意答案

经过长时间的搜索，感谢Jeff Orris和Lad2025。 我已经得出结论，CLR是最好的方法。 为了帮助其他SO用户，我现在将发布一些内容。

第一。 这是CLR的基本VB代码。 要安装它，请按照我的问题的评论。

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class ExcelFunctions <Microsoft.SqlServer.Server.SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _ Public Shared Function fPMT(ByVal Rate As Double, ByVal NPer As Double, ByVal PV As Double, Optional ByVal FV As Double = 0, Optional ByVal Type As Byte = 0) As SqlDouble Dim PMTS As Double If Type = 0 Then PMTS = Pmt(Rate, NPer, PV, FV, DueDate.EndOfPeriod) ElseIf Type = 1 Then PMTS = Pmt(Rate, NPer, PV, FV, DueDate.BegOfPeriod) End If Return New SqlDouble(PMTS) End Function <Microsoft.SqlServer.Server.SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _ Public Shared Function fNPER(ByVal Rate As Double, ByVal PMTS As Double, ByVal PV As Double, Optional ByVal FV As Double = 0, Optional ByVal Type As Byte = 0) As SqlDouble Dim NPERS As Double If Type = 0 Then NPERS = NPer(Rate, PMTS, PV, FV, DueDate.EndOfPeriod) ElseIf Type = 1 Then NPERS = NPer(Rate, PMTS, PV, FV, DueDate.BegOfPeriod) End If Return New SqlDouble(NPERS) End Function <Microsoft.SqlServer.Server.SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _ Public Shared Function fPV(ByVal Rate As Double, ByVal NPER As Double, ByVal PMTS As Double, Optional ByVal FV As Double = 0, Optional ByVal Type As Byte = 0) As SqlDouble Dim PVs As Double If Type = 0 Then PVs = PV(Rate, NPER, PMTS, FV, DueDate.EndOfPeriod) ElseIf Type = 1 Then PVs = PV(Rate, NPER, PMTS, FV, DueDate.BegOfPeriod) End If Return New SqlDouble(PVs) End Function <Microsoft.SqlServer.Server.SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _ Public Shared Function fFV(ByVal Rate As Double, ByVal NPER As Double, ByVal PMTS As Double, Optional ByVal PV As Double = 0, Optional ByVal Type As Byte = 0) As SqlDouble Dim FVs As Double If Type = 0 Then FVs = FV(Rate, NPER, PMTS, PV, DueDate.EndOfPeriod) ElseIf Type = 1 Then FVs = FV(Rate, NPER, PMTS, PV, DueDate.BegOfPeriod) End If Return New SqlDouble(FVs) End Function <Microsoft.SqlServer.Server.SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _ Public Shared Function fRATE(ByVal NPER As Double, ByVal PMTS As Double, Optional ByVal PV As Double = 0, Optional ByVal FV As Double = 0, Optional ByVal Type As Byte = 0) As SqlDouble Dim Rates As Double If Type = 0 Then Rates = Rate(NPER, PMTS, PV, FV, DueDate.EndOfPeriod, 0.01) ElseIf Type = 1 Then Rates = Rate(NPER, PMTS, PV, FV, DueDate.BegOfPeriod, 0.01) End If Return New SqlDouble(Rates) End Function End Class

现在对于那些对VB代码不感兴趣或者没有软件的人。 下面是将为此代码创建函数/程序集（dll）的SQL脚本，可以在SQL Server上运行。 这就是我最终使用的。 我为大会疯狂而道歉，但你理解:)

CREATE ASSEMBLY [CLRExcel_Financial_Functions] AUTHORIZATION [dbo] FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01040083804F560000000000000000E00002210B010B00000A0000000E000000000000DE29000000200000004000000000400000200000000200000400000000000000040000000000000000A0000000040000000000000200408500001000001000000000100000100000000000001000000000000000000000008C2900004F000000006000008808000000000000000000000000000000000000008000000C000000004000001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000E409000000200000000A000000040000000000000000000000000000200000602E73646174610000C20000000040000000020000000E0000000000000000000000000000400000C02E727372630000008808000000600000000A000000100000000000000000000000000000400000402E72656C6F6300000C0000000080000000020000001A0000000000000000000000000000400000420000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0290000000000004800000002000500B0210000DC07000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002602280100000A00002A0000133005003300000001000011000E0416330D0203040516280200000A0B2B100E0417330B0203040517280200000A0B00120207280300000A00080A2B00062A00133005003300000002000011000E0416330D0203040516280400000A0B2B100E0417330B0203040517280400000A0B00120207280300000A00080A2B00062A00133005003300000003000011000E0416330D0203040516280500000A0B2B100E0417330B0203040517280500000A0B00120207280300000A00080A2B00062A00133005003300000004000011000E0416330D0203040516280600000A0B2B100E0417330B0203040517280600000A0B00120207280300000A00080A2B00062A00133006004500000005000011000E041633160203040516237B14AE47E17A843F280700000A0B2B190E041733140203040517237B14AE47E17A843F280700000A0B00120207280300000A00080A2B00062A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000050030000237E0000BC030000EC02000023537472696E677300000000A80600000800000023555300B0060000100000002347554944000000C00600001C01000023426C6F620000000000000002000001471D02000900000000FA2533001600000100000013000000020000000600000019000000150000000B000000120000000500000001000000030000000000C80201000000000006005C0055000E008A0075000A00CC0013000A00D60013000600F500E2000E002D01120106004201E2001F00560100000600850165010600A50165010600E201C3010600F001C30106000402550006002C021A02060047021A02060062021A0206007B021A02060094021A020600B1021A020000000001000000000001000100010000002900380005000100010050200000000006186300130001005C200000000016009400170001009C20000000001600AE0017000600DC20000000001600B90017000B001C21000000001600C200170010005C21000000001600C60017001500000001009900000002009E0000000300A30010100400A60010100500A90000000100990000000200B40000000300A30010100400A60010100500A90000000100990000000200BD0000000300B40010100400A60010100500A90000000100990000000200BD0000000300B40010100400A30010100500A90000000100BD0000000200B40010100300A30010100400A60010100500A9000900630013001900DE002C0011006300360019009E002C001900A3002C001900A6002C00190099004300290063001300310063001300390063007800490063007E005100630013005900630083006100630088006900630088007100630083007900630083008100630083008900630083009100630083009900630083000D0011002100050015002A000D0025002100050029002A000D003900210005003D002A000D004D002100050051002A000D005D0021000D0061002100050065002A00200043004E002E0063009F002E006B00BE002E007300E8002E00A300E8002E0053008D002E005B0096002E008300E8002E009B00E8002E007B00EE002E00AB000C012E008B00F4002E0093000C0140004B00530060004B00530080004B005300A0004B005300C0004B0053003B003B003B003B003B000480000001000000AA1620550000000000003800000002000000000000000000000001000A00000000000800000000000000000000000A0013000000000002000000000000000000000001006900000000000000003C4D6F64756C653E006D73636F726C6962004D6963726F736F66742E56697375616C426173696300457863656C46756E6374696F6E7300434C52457863656C5F46696E616E6369616C5F46756E6374696F6E730053797374656D004F626A656374002E63746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C446F75626C650066504D540052617465004E506572005056004656005479706500664E50455200504D545300665056004E504552006646560066524154450046696E616E6369616C004475654461746500506D740053797374656D2E446961676E6F73746963730044656275676765724E6F6E55736572436F6465417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053797374656D2E52756E74696D652E496E7465726F705365727669636573004775696441747472696275746500436F6D56697369626C6541747472696275746500434C53436F6D706C69616E744174747269627574650053797374656D2E5265666C656374696F6E00417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C6541747472696275746500434C52457863656C5F46696E616E6369616C5F46756E6374696F6E732E646C6C000000000003200000000000FE06D9D2A5500340965A8DA858E6528E0008B77A5C561934E08908B03F5F7F11D50A3A0320000109000511090D0D0D0D0508000000000000000001000900050D0D0D0D0D1111042001010D07070311090D11090A00060D0D0D0D0D11110D0401000000240100020054020F497344657465726D696E697374696301540209497350726563697365010520010111210420010108042001010E04200101020801000301000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773012901002430303830613839312D323563642D343434332D393236662D663332666532333635666536000005010000000005010001000017010012436F7079726967687420C2A920203230313300000F01000A434C52457863656C46560000B42900000000000000000000CE290000002000000000000000000000000000000000000000000000C0290000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000083804F560000000002000000A60000001C4000001C0E000052534453BEBCC0A2671A8841903983FBE9D679E401000000433A5C55736572735C50545265686D5C446F63756D656E74735C5446535C57696E646F77735F417070735C434C52457863656C5F46696E616E6369616C5F46756E6374696F6E5C434C52457863656C5F46696E616E6369616C5F46756E6374696F6E5C6F626A5C44656275675C434C52457863656C5F46696E616E6369616C5F46756E6374696F6E732E706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000030003000000280000800E000000480000801000000060000080000000000000000000000000000002000200000078000080030000009000008000000000000000000000000000000100007F0000A80000800000000000000000000000000000010001000000C00000800000000000000000000000000000010000000000D80000000000000000000000000000000000010000000000E80000000000000000000000000000000000010000000000F800000000000000000000000000000000000100000000000801000050640000E80200000000000000000000386700002801000000000000000000006068000022000000000000000000000018610000380300000000000000000000380334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001002055AA16000001002055AA163F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00498020000010053007400720069006E006700460069006C00650049006E0066006F00000074020000010030003000300030003000340062003000000040000B000100460069006C0065004400650073006300720069007000740069006F006E000000000043004C00520045007800630065006C00460056000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0035003800300032002E00320031003700390032000000000064002100010049006E007400650072006E0061006C004E0061006D006500000043004C00520045007800630065006C005F00460069006E0061006E006300690061006C005F00460075006E006300740069006F006E0073002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100330000006C00210001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000043004C00520045007800630065006C005F00460069006E0061006E006300690061006C005F00460075006E006300740069006F006E0073002E0064006C006C000000000038000B000100500072006F0064007500630074004E0061006D0065000000000043004C00520045007800630065006C00460056000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0035003800300032002E00320031003700390032000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0035003800300032002E003200310037003900320000000000280000002000000040000000010004000000000080020000000000000000000000000000000000000000000000008000008000000080800080000000800080008080000080808000C0C0C0000000FF0000FF000000FFFF00FF000000FF00FF00FFFF0000FFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007777777777777777777777777777700444444444444444444444444444447004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF4700488888888888888888888888888847004444444444444444444444444444470044C4C4C4C4C4C4C4C4C4ECECE49747004CCCCCCCCCCCCCCCCCCCCCCCCCCC40000444444444444444444444444444000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFC00000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000003C0000007FFFFFFFFFFFFFFFFFFFFFFFF2800000010000000200000000100040000000000C0000000000000000000000000000000000000000000000000008000008000000080800080000000800080008080000080808000C0C0C0000000FF0000FF000000FFFF00FF000000FF00FF00FFFF0000FFFFFF000000000000000000077777777777777744444444444444474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF84748888888888888474CCCCCCCCCCCCC47C4444444444444C000000000000000000000000000000000FFFF000080000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000FFFF0000FFFF00000000010002002020100001000400E80200000200101010000100040028010000030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000E03900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WITH PERMISSION_SET = SAFE GO CREATE FUNCTION [dbo].[fRATE](@NPER [float], @PMTS [float], @PV [float], @FV [float], @Type [tinyint]) RETURNS [float] WITH EXECUTE AS CALLER AS EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[fRATE] GO CREATE FUNCTION [dbo].[fPV](@Rate [float], @NPER [float], @PMTS [float], @FV [float], @Type [tinyint]) RETURNS [float] WITH EXECUTE AS CALLER AS EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[fPV] GO CREATE FUNCTION [dbo].[fPMT](@Rate [float], @NPer [float], @PV [float], @FV [float], @Type [tinyint]) RETURNS [float] WITH EXECUTE AS CALLER AS EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[fPMT] GO CREATE FUNCTION [dbo].[fNPER](@Rate [float], @PMTS [float], @PV [float], @FV [float], @Type [tinyint]) RETURNS [float] WITH EXECUTE AS CALLER AS EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[fNPER] GO CREATE FUNCTION [dbo].[fFV](@Rate [float], @NPER [float], @PMTS [float], @PV [float], @Type [tinyint]) RETURNS [float] WITH EXECUTE AS CALLER AS EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[fFV] GO

After a long search, on thanks to Jeff Orris and Lad2025 . I have concluded the a CLR is the best method. To help the other SO users, I will post a few things now.

First. This is the basic VB code for the CLR. To install it follow the comments on my question.

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class ExcelFunctions <Microsoft.SqlServer.Server.SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _ Public Shared Function fPMT(ByVal Rate As Double, ByVal NPer As Double, ByVal PV As Double, Optional ByVal FV As Double = 0, Optional ByVal Type As Byte = 0) As SqlDouble Dim PMTS As Double If Type = 0 Then PMTS = Pmt(Rate, NPer, PV, FV, DueDate.EndOfPeriod) ElseIf Type = 1 Then PMTS = Pmt(Rate, NPer, PV, FV, DueDate.BegOfPeriod) End If Return New SqlDouble(PMTS) End Function <Microsoft.SqlServer.Server.SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _ Public Shared Function fNPER(ByVal Rate As Double, ByVal PMTS As Double, ByVal PV As Double, Optional ByVal FV As Double = 0, Optional ByVal Type As Byte = 0) As SqlDouble Dim NPERS As Double If Type = 0 Then NPERS = NPer(Rate, PMTS, PV, FV, DueDate.EndOfPeriod) ElseIf Type = 1 Then NPERS = NPer(Rate, PMTS, PV, FV, DueDate.BegOfPeriod) End If Return New SqlDouble(NPERS) End Function <Microsoft.SqlServer.Server.SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _ Public Shared Function fPV(ByVal Rate As Double, ByVal NPER As Double, ByVal PMTS As Double, Optional ByVal FV As Double = 0, Optional ByVal Type As Byte = 0) As SqlDouble Dim PVs As Double If Type = 0 Then PVs = PV(Rate, NPER, PMTS, FV, DueDate.EndOfPeriod) ElseIf Type = 1 Then PVs = PV(Rate, NPER, PMTS, FV, DueDate.BegOfPeriod) End If Return New SqlDouble(PVs) End Function <Microsoft.SqlServer.Server.SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _ Public Shared Function fFV(ByVal Rate As Double, ByVal NPER As Double, ByVal PMTS As Double, Optional ByVal PV As Double = 0, Optional ByVal Type As Byte = 0) As SqlDouble Dim FVs As Double If Type = 0 Then FVs = FV(Rate, NPER, PMTS, PV, DueDate.EndOfPeriod) ElseIf Type = 1 Then FVs = FV(Rate, NPER, PMTS, PV, DueDate.BegOfPeriod) End If Return New SqlDouble(FVs) End Function <Microsoft.SqlServer.Server.SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _ Public Shared Function fRATE(ByVal NPER As Double, ByVal PMTS As Double, Optional ByVal PV As Double = 0, Optional ByVal FV As Double = 0, Optional ByVal Type As Byte = 0) As SqlDouble Dim Rates As Double If Type = 0 Then Rates = Rate(NPER, PMTS, PV, FV, DueDate.EndOfPeriod, 0.01) ElseIf Type = 1 Then Rates = Rate(NPER, PMTS, PV, FV, DueDate.BegOfPeriod, 0.01) End If Return New SqlDouble(Rates) End Function End Class

Now for those of you who are not interested in VB code or perhaps do not have the software. Below is the SQL script that will create the function / assemblies (dll) for this code and can be run on SQL server. This is what I ended up using. I apologize for the assembly being insane, but you understand :)

CREATE ASSEMBLY [CLRExcel_Financial_Functions] AUTHORIZATION [dbo] FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01040083804F560000000000000000E00002210B010B00000A0000000E000000000000DE29000000200000004000000000400000200000000200000400000000000000040000000000000000A0000000040000000000000200408500001000001000000000100000100000000000001000000000000000000000008C2900004F000000006000008808000000000000000000000000000000000000008000000C000000004000001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000E409000000200000000A000000040000000000000000000000000000200000602E73646174610000C20000000040000000020000000E0000000000000000000000000000400000C02E727372630000008808000000600000000A000000100000000000000000000000000000400000402E72656C6F6300000C0000000080000000020000001A0000000000000000000000000000400000420000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C0290000000000004800000002000500B0210000DC07000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002602280100000A00002A0000133005003300000001000011000E0416330D0203040516280200000A0B2B100E0417330B0203040517280200000A0B00120207280300000A00080A2B00062A00133005003300000002000011000E0416330D0203040516280400000A0B2B100E0417330B0203040517280400000A0B00120207280300000A00080A2B00062A00133005003300000003000011000E0416330D0203040516280500000A0B2B100E0417330B0203040517280500000A0B00120207280300000A00080A2B00062A00133005003300000004000011000E0416330D0203040516280600000A0B2B100E0417330B0203040517280600000A0B00120207280300000A00080A2B00062A00133006004500000005000011000E041633160203040516237B14AE47E17A843F280700000A0B2B190E041733140203040517237B14AE47E17A843F280700000A0B00120207280300000A00080A2B00062A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000050030000237E0000BC030000EC02000023537472696E677300000000A80600000800000023555300B0060000100000002347554944000000C00600001C01000023426C6F620000000000000002000001471D02000900000000FA2533001600000100000013000000020000000600000019000000150000000B000000120000000500000001000000030000000000C80201000000000006005C0055000E008A0075000A00CC0013000A00D60013000600F500E2000E002D01120106004201E2001F00560100000600850165010600A50165010600E201C3010600F001C30106000402550006002C021A02060047021A02060062021A0206007B021A02060094021A020600B1021A020000000001000000000001000100010000002900380005000100010050200000000006186300130001005C200000000016009400170001009C20000000001600AE0017000600DC20000000001600B90017000B001C21000000001600C200170010005C21000000001600C60017001500000001009900000002009E0000000300A30010100400A60010100500A90000000100990000000200B40000000300A30010100400A60010100500A90000000100990000000200BD0000000300B40010100400A60010100500A90000000100990000000200BD0000000300B40010100400A30010100500A90000000100BD0000000200B40010100300A30010100400A60010100500A9000900630013001900DE002C0011006300360019009E002C001900A3002C001900A6002C00190099004300290063001300310063001300390063007800490063007E005100630013005900630083006100630088006900630088007100630083007900630083008100630083008900630083009100630083009900630083000D0011002100050015002A000D0025002100050029002A000D003900210005003D002A000D004D002100050051002A000D005D0021000D0061002100050065002A00200043004E002E0063009F002E006B00BE002E007300E8002E00A300E8002E0053008D002E005B0096002E008300E8002E009B00E8002E007B00EE002E00AB000C012E008B00F4002E0093000C0140004B00530060004B00530080004B005300A0004B005300C0004B0053003B003B003B003B003B000480000001000000AA1620550000000000003800000002000000000000000000000001000A00000000000800000000000000000000000A0013000000000002000000000000000000000001006900000000000000003C4D6F64756C653E006D73636F726C6962004D6963726F736F66742E56697375616C426173696300457863656C46756E6374696F6E7300434C52457863656C5F46696E616E6369616C5F46756E6374696F6E730053797374656D004F626A656374002E63746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C446F75626C650066504D540052617465004E506572005056004656005479706500664E50455200504D545300665056004E504552006646560066524154450046696E616E6369616C004475654461746500506D740053797374656D2E446961676E6F73746963730044656275676765724E6F6E55736572436F6465417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053797374656D2E52756E74696D652E496E7465726F705365727669636573004775696441747472696275746500436F6D56697369626C6541747472696275746500434C53436F6D706C69616E744174747269627574650053797374656D2E5265666C656374696F6E00417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C6541747472696275746500434C52457863656C5F46696E616E6369616C5F46756E6374696F6E732E646C6C000000000003200000000000FE06D9D2A5500340965A8DA858E6528E0008B77A5C561934E08908B03F5F7F11D50A3A0320000109000511090D0D0D0D0508000000000000000001000900050D0D0D0D0D1111042001010D07070311090D11090A00060D0D0D0D0D11110D0401000000240100020054020F497344657465726D696E697374696301540209497350726563697365010520010111210420010108042001010E04200101020801000301000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773012901002430303830613839312D323563642D343434332D393236662D663332666532333635666536000005010000000005010001000017010012436F7079726967687420C2A920203230313300000F01000A434C52457863656C46560000B42900000000000000000000CE290000002000000000000000000000000000000000000000000000C0290000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000083804F560000000002000000A60000001C4000001C0E000052534453BEBCC0A2671A8841903983FBE9D679E401000000433A5C55736572735C50545265686D5C446F63756D656E74735C5446535C57696E646F77735F417070735C434C52457863656C5F46696E616E6369616C5F46756E6374696F6E5C434C52457863656C5F46696E616E6369616C5F46756E6374696F6E5C6F626A5C44656275675C434C52457863656C5F46696E616E6369616C5F46756E6374696F6E732E706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000030003000000280000800E000000480000801000000060000080000000000000000000000000000002000200000078000080030000009000008000000000000000000000000000000100007F0000A80000800000000000000000000000000000010001000000C00000800000000000000000000000000000010000000000D80000000000000000000000000000000000010000000000E80000000000000000000000000000000000010000000000F800000000000000000000000000000000000100000000000801000050640000E80200000000000000000000386700002801000000000000000000006068000022000000000000000000000018610000380300000000000000000000380334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001002055AA16000001002055AA163F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00498020000010053007400720069006E006700460069006C00650049006E0066006F00000074020000010030003000300030003000340062003000000040000B000100460069006C0065004400650073006300720069007000740069006F006E000000000043004C00520045007800630065006C00460056000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0035003800300032002E00320031003700390032000000000064002100010049006E007400650072006E0061006C004E0061006D006500000043004C00520045007800630065006C005F00460069006E0061006E006300690061006C005F00460075006E006300740069006F006E0073002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100330000006C00210001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000043004C00520045007800630065006C005F00460069006E0061006E006300690061006C005F00460075006E006300740069006F006E0073002E0064006C006C000000000038000B000100500072006F0064007500630074004E0061006D0065000000000043004C00520045007800630065006C00460056000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0035003800300032002E00320031003700390032000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0035003800300032002E003200310037003900320000000000280000002000000040000000010004000000000080020000000000000000000000000000000000000000000000008000008000000080800080000000800080008080000080808000C0C0C0000000FF0000FF000000FFFF00FF000000FF00FF00FFFF0000FFFFFF0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007777777777777777777777777777700444444444444444444444444444447004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF47004FFFFFFFFFFFFFFFFFFFFFFFFFFF4700488888888888888888888888888847004444444444444444444444444444470044C4C4C4C4C4C4C4C4C4ECECE49747004CCCCCCCCCCCCCCCCCCCCCCCCCCC40000444444444444444444444444444000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFC00000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000001800000018000000180000003C0000007FFFFFFFFFFFFFFFFFFFFFFFF2800000010000000200000000100040000000000C0000000000000000000000000000000000000000000000000008000008000000080800080000000800080008080000080808000C0C0C0000000FF0000FF000000FFFF00FF000000FF00FF00FFFF0000FFFFFF000000000000000000077777777777777744444444444444474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF8474FFFFFFFFFFFF84748888888888888474CCCCCCCCCCCCC47C4444444444444C000000000000000000000000000000000FFFF000080000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000FFFF0000FFFF00000000010002002020100001000400E80200000200101010000100040028010000030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000E03900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WITH PERMISSION_SET = SAFE GO CREATE FUNCTION [dbo].[fRATE](@NPER [float], @PMTS [float], @PV [float], @FV [float], @Type [tinyint]) RETURNS [float] WITH EXECUTE AS CALLER AS EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[fRATE] GO CREATE FUNCTION [dbo].[fPV](@Rate [float], @NPER [float], @PMTS [float], @FV [float], @Type [tinyint]) RETURNS [float] WITH EXECUTE AS CALLER AS EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[fPV] GO CREATE FUNCTION [dbo].[fPMT](@Rate [float], @NPer [float], @PV [float], @FV [float], @Type [tinyint]) RETURNS [float] WITH EXECUTE AS CALLER AS EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[fPMT] GO CREATE FUNCTION [dbo].[fNPER](@Rate [float], @PMTS [float], @PV [float], @FV [float], @Type [tinyint]) RETURNS [float] WITH EXECUTE AS CALLER AS EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[fNPER] GO CREATE FUNCTION [dbo].[fFV](@Rate [float], @NPER [float], @PMTS [float], @PV [float], @Type [tinyint]) RETURNS [float] WITH EXECUTE AS CALLER AS EXTERNAL NAME [CLRExcel_Financial_Functions].[CLRExcel_Financial_Functions.ExcelFunctions].[fFV] GO

更多推荐

本文发布于:2023-07-22 02:16:00，感谢您对本站的认可！
本文链接:https://www.elefans.com/category/jswz/34/1216030.html
版权声明:本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系，我们将在24小时内删除。
本文标签：年金   函数   PV   TVM   SQL

发布评论

评论列表 （有 0 条评论）

最近发表

草根站长

>www.elefans.com

编程频道|电子爱好者 - 技术资讯及电子产品介绍！

热门文章

标签列表