字符串形式
CREATE proc [dbo].[ufn_IRR]
(
@strIDs table(a int),
@guess DECIMAL(30,10)
)
RETURNS DECIMAL(30, 10)
AS
BEGIN
DECLARE @t_IDs TABLE (
id INT IDENTITY(0, 1),
value DECIMAL(30, 10)
)
DECLARE @strID VARCHAR(12),@sepPos INT,@NPV DECIMAL(30, 10)
SET @strIDs = COALESCE(@strIDs + ',', '')
SET @sepPos = CHARINDEX(',', @strIDs)
WHILE @sepPos > 0
BEGIN
SET @strID = LEFT(@strIDs, @sepPos - 1)
INSERT INTO @t_IDs ( value ) SELECT ( CAST(@strID AS DECIMAL(20, 10)) ) WHERE ISNUMERIC(@strID) = 1
SET @strIDs = RIGHT(@strIDs, DATALENGTH(@strIDs) - @sepPos)
SET @sepPos = CHARINDEX(',', @strIDs)
END
SET @guess = CASE WHEN ISNULL(@guess, 0) <= 0 THEN 0.00001 ELSE @guess END
SELECT @NPV = SUM(value / POWER(1 + @guess, id)) FROM @t_IDs
WHILE @NPV > 0
BEGIN
SET @guess = @guess + 0.00001
SELECT @NPV = SUM(value / POWER(1 + @guess, id)) FROM @t_IDs
END
RETURN @guess
END
--SELECT dbo.ufn_IRR('-4000,1200,1410,1875,1050',0.00001)
表形式
go
DECLARE @t_IDs as IRRTable
insert @t_IDs values(0,-69773.84)
insert @t_IDs values(1,5172.49049333333)
insert @t_IDs values(2,5156.81061666667)
insert @t_IDs values(3,5141.13074)
insert @t_IDs values(4,5125.45086333333)
insert @t_IDs values(5,5109.77098666667)
insert @t_IDs values(6,5094.09111)
insert @t_IDs values(7,5078.41123333333)
insert @t_IDs values(8,5062.73135666667)
insert @t_IDs values(9,2608.98148)
insert @t_IDs values(10,2601.02215833333)
insert @t_IDs values(11,2593.06283666667)
insert @t_IDs values(12,2585.103515)
insert @t_IDs values(13,2577.14419333333)
insert @t_IDs values(14,2569.18487166667)
insert @t_IDs values(15,2561.22555)
insert @t_IDs values(16,2553.26622833333)
insert @t_IDs values(17,2545.30690666667)
insert @t_IDs values(18,2537.347585)
insert @t_IDs values(19,2529.38826333333)
insert @t_IDs values(20,2521.30894166667)
SELECT dbo.[ufn_IRR] (@t_IDs,0.000001)
go
create type IRRTable as Table
(id int,value DECIMAL(30, 10))
go
CREATE FUNCTION [dbo].[ufn_IRR]
(
@t_IDs IRRTable READONLY,
@guess DECIMAL(30,10)
)
RETURNS DECIMAL(30, 10)
as
begin
DECLARE @NPV DECIMAL(30, 10)
SET @guess = CASE WHEN ISNULL(@guess, 0) <= 0 THEN 0.00001 ELSE @guess END
SELECT @NPV = SUM(value / POWER(1 + @guess, id)) FROM @t_IDs
WHILE @NPV > 0
BEGIN
SET @guess = @guess + 0.00001
SELECT @NPV = SUM(value / POWER(1 + @guess, id)) FROM @t_IDs
END
return @guess*12
end
go
字符串SQL版本
CREATE Proc [dbo].[DSF_IRR]
(
@sql Nvarchar(max),
@guess DECIMAL(30,10)
)
as
begin
DECLARE @NPV DECIMAL(30, 10)
create table #tmp(id INT IDENTITY(0, 1),value decimal(38,10))
DECLARE @t_IDs table(id int,value decimal(38,10))
set @sql='insert into #tmp (value)'+ @sql
exec(@sql)
insert into @t_IDs
select * from #tmp
SET @guess = CASE WHEN ISNULL(@guess, 0) <= 0 THEN 0.00001 ELSE @guess END
SELECT @NPV = SUM(value / POWER(1 + @guess, id)) FROM @t_IDs
WHILE @NPV > 0
BEGIN
SET @guess = @guess + 0.00001
SELECT @NPV = SUM(value / POWER(1 + @guess, id)) FROM @t_IDs
END
select @guess*12 as IRR
end
go
exec [dbo].[DSF_IRR]
'select INSTM as value from [DFSA].[dbo].[PaymentSchedule] WHERE [Category]=''Posting7'' AND [Restructure]=''R'' order by TNR'
,0.0001