Non cursor cumulative percentage calc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Non cursor cumulative percentage calc

Author

Topic

yonabout
Posting Yak Master

112 Posts

Posted - 2008-12-17 : 10:18:11

Hi,

I need to write a calculation that can take a value (at a date) and increase it by an annual percentage between that date and now.

I can figure out how to do it using a cursor, but I'm going to need to do it in bulk, and I can see it taking a prohibitively long time.

So say I have a figure of 1000 @ 01/01/2000.

I then have a table of percentage increases that will look something like this:

Year Increase
2000 1
2001 1
2002 1
2003 1
2004 1
2005 1
2006 1
2007 1
2008 1

So the value of my original 1000 (in 2000) will now be 1082.856 (worked out like this):

2000 (1000 + 1% of 1000) = 1010
2001 (1010 + 1% of 1010) = 1020.1

etc. etc

2008 (1072.135 + 1% of 1072.135) = 1082.856

So anyone got any ideas of the most optimised way I can do this in squizzle?

Cheers,

Yonabout

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-12-17 : 10:40:05

You can try something like this:
DECLARE @Table TABLE
( CurrYear int
,Pct int
,Total float)

DECLARE @id int
DECLARE @Total float

SET @id = 1
SET @total = 1000

INSERT INTO @Table
SELECT 2001, 1,0 UNION ALL
SELECT 2002, 1,0 UNION ALL
SELECT 2003, 1,0 UNION ALL
SELECT 2004, 1,0 UNION ALL
SELECT 2005, 1,0 UNION ALL
SELECT 2006, 1 ,0 UNION ALL
SELECT 2007 ,1,0 UNION ALL
SELECT 2008 ,1,0

UPDATE @Table
SET @Total = Total = @Total*(1+pct*1.0/100)

select * from @table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-17 : 10:43:51

1082.856 only? Aren't you missing a year?

DECLARE	@Sample TABLE
	(
		Year SMALLINT,
		Increase TINYINT
	)

INSERT	@Sample
SELECT	2000, 1 UNION ALL	-- 1010
SELECT	2001, 1 UNION ALL	-- 1020.1
SELECT	2002, 1 UNION ALL	-- 1030.301
SELECT	2003, 1 UNION ALL	-- 1040.60401
SELECT	2004, 1 UNION ALL	-- 1051.01005
SELECT	2005, 1 UNION ALL	-- 1061.520151
SELECT	2006, 1 UNION ALL	-- 1072.135353
SELECT	2007, 1 UNION ALL	-- 1082.856707
SELECT	2008, 1			-- 1093.685274

DECLARE	@raise DECIMAL(38, 19)

SET	@Raise = 1000

SELECT	@Raise = @Raise * CAST(1.0E + increase / 100.0E AS DECIMAL(38, 19))
FROM	@Sample

SELECT	@Raise

E 12°55'05.63"
N 56°04'39.26"

yonabout
Posting Yak Master

112 Posts

Posted - 2008-12-17 : 10:53:15

Thanks both for your help - both solutions work great

Cheers,

Yonabout

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-12-17 : 10:53:36

Oops, I started at 2001 instead of 2000!

Jim

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-12-17 : 11:12:58

Yonabout, I assume you are aware that none of these solutions will work if the increase is not identical from year to year, and then you are back to the cursor solution.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-17 : 11:37:57

Why do you say that?

quote:
Originally posted by blindman

Yonabout, I assume you are aware that none of these solutions will work if the increase is not identical from year to year, and then you are back to the cursor solution.

If you only want get ending value there is no difference if you multiply
1.1 * 1.4 * 1.2 * 1.3 or 1.1 * 1.2 * 1.3 * 1.4. You get same value.

DECLARE	@Sample TABLE
	(
		Year SMALLINT PRIMARY KEY CLUSTERED,
		Increase DECIMAL(38, 19)
	)

INSERT	@Sample
SELECT	2000, 1.2 UNION ALL	-- 1012.000000
SELECT	2001, 1.5 UNION ALL	-- 1027.180000
SELECT	2002, 1.8 UNION ALL	-- 1045.669240
SELECT	2003, 2.4 UNION ALL	-- 1070.765302
SELECT	2004, 3.1 UNION ALL	-- 1103.959026
SELECT	2005, 1.7 UNION ALL	-- 1122.726330
SELECT	2006, 4.3 UNION ALL	-- 1171.003562
SELECT	2007, 1.6 UNION ALL	-- 1189.739619
SELECT	2008, 1.1		-- 1202.826755

SELECT 1000 * 1.012 * 1.015 * 1.018 * 1.024 * 1.031 * 1.017 * 1.043 * 1.016 * 1.011 UNION ALL
SELECT 1000 * 1.043 * 1.012 * 1.024 * 1.015 * 1.018 * 1.031 * 1.017 * 1.011 * 1.016

E 12°55'05.63"
N 56°04'39.26"

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-12-17 : 12:30:12

My bad. I was thinking about compounding, which would apply to investments but not to this salary scenario.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-12-17 : 14:24:57

As a single SELECT statement:

DECLARE	@Sample TABLE
	(Year SMALLINT,
	Increase TINYINT)

INSERT	@Sample
SELECT	2000, 1 UNION ALL	-- 1010
SELECT	2001, 1 UNION ALL	-- 1020.1
SELECT	2002, 1 UNION ALL	-- 1030.301
SELECT	2003, 1 UNION ALL	-- 1040.60401
SELECT	2004, 1 UNION ALL	-- 1051.01005
SELECT	2005, 1 UNION ALL	-- 1061.520151
SELECT	2006, 1 UNION ALL	-- 1072.135353
SELECT	2007, 1 UNION ALL	-- 1082.856707
SELECT	2008, 1			-- 1093.685274

--Single select statement:
select	1000*exp(sum(log(1.0E + Increase/100.0E)))
from	@Sample

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-12-17 : 17:10:29

[code]select
[Year] = 1999+a.Number,
[New Salary] =
convert(numeric(10,2),round(power(1.01E,a.Number)*1000E,2))
from
F_TABLE_NUMBER_RANGE(1,9) a
order by
1

Results:
Year New Salary
----------- ------------
2000 1010.00
2001 1020.10
2002 1030.30
2003 1040.60
2004 1051.01
2005 1061.52
2006 1072.14
2007 1082.86
2008 1093.69

(9 row(s) affected)[/code]

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 02:27:44

See http://weblogs.sqlteam.com/peterl/archive/2008/11/19/How-to-get-the-productsum-from-a-table.aspx
hoe to deal with productsum that is negative or zero.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources