Running Totals - SQL Server Forums

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)

Running Totals

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-09 : 09:42:40

I gotta search the site more but, and this is a presentation issue, and I may need to convince people of this, BUT

If I haveRent and Tax, and they want to accrue the tax over years, kind of like

Year 1 2.50 + (2.50 * .04)
Year 2 2.50 + ((2.50 * .04)*.04)
Year 3 2.50 + (((2.50 * .04)*.04)*.04)
ect

This is what I've come up with so far, but it's not even close

btw, does the above even make sense? They are doing it this way in Excels sheets right now.


CREATE TABLE #temp(Rent money, [Year] int, Tax decimal(12,4))
GO

INSERT INTO #temp(Rent, [Year], Tax)
SELECT 25, 1, .1 UNION ALL
SELECT 25, 2, .1 UNION ALL
SELECT 25, 3, .1 UNION ALL
SELECT 25, 4, .1
GO

SELECT * FROM #temp

SELECT a.[Year], ((b.Rent + b.Rent*b.Tax)*a.Tax) AS Rent_Tax
, b.[Year] - 1 AS PriorYear,b.*
FROM  #temp a LEFT JOIN #temp b ON a.[Year] = b.[Year] - 1
GO

DROP TABLE #temp
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 09:45:22

Where is the SQLTeam front-end presentation expert ?

KH
[spoiler]Time is always against us[/spoiler]

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-09 : 10:10:13

quote:
Originally posted by khtan

Where is the SQLTeam front-end presentation expert ?

KH
[spoiler]Time is always against us[/spoiler]

Right....that's what I thought...maybe Sum all previous years or something

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 10:29:47

how about a UDF that returns the product of tax with no of years as input ?

KH
[spoiler]Time is always against us[/spoiler]

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-09 : 10:48:58

I think the tax can be different between years, but thank for the suggestion

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-09 : 11:06:10

Is this what you're looking for?

SELECT *, RENT + RENT* POWER(TAX +1,[YEAR])
from @tbl

Jim

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-10 : 09:29:56

Hey just a thought, but isn't this a simple amortization formula?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-10 : 09:36:23

yes it is.
economics 101 lesson 2

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-10 : 09:40:59

I failed economics....I'm gonna go google the forumla, but does anyone know it off hand?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-10 : 10:16:50

didn't jimf give it to you?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-10 : 10:54:42

Yeah, I guess he did, but I had to worry about year 1


CREATE TABLE #temp(Tax money, [Year] int, TaxInc decimal(12,2))
GO

INSERT INTO #temp(Tax, [Year], TaxInc)
SELECT 2.5, 1, 4 UNION ALL
SELECT 2.5, 2, 4 UNION ALL
SELECT 2.5, 3, 4 UNION ALL
SELECT 2.5, 4, 4 UNION ALL
SELECT 2.5, 5, 4 UNION ALL
SELECT 2.5, 6, 4 UNION ALL
SELECT 2.5, 7, 4 UNION ALL
SELECT 2.5, 8, 4 UNION ALL
SELECT 2.5, 9, 4 UNION ALL
SELECT 2.5, 10, 4 UNION ALL
SELECT 2.5, 11, 4 UNION ALL
SELECT 2.5, 12, 4 UNION ALL
SELECT 2.5, 13, 4 UNION ALL
SELECT 2.5, 14, 4 UNION ALL
SELECT 2.5, 15, 4 UNION ALL
SELECT 2.5, 16, 4 UNION ALL
SELECT 2.5, 17, 4 UNION ALL
SELECT 2.5, 18, 4 UNION ALL
SELECT 2.5, 19, 4 UNION ALL
SELECT 2.5, 20, 4
GO

   SELECT [Year], Tax
     FROM #Temp 
    WHERE [Year] = 1
UNION ALL
   SELECT [Year]+1, CONVERT(decimal(19,2),Tax*POWER(1+(TaxInc/100),[Year])) AS Taxes 
     FROM #temp
    WHERE [Year] < 20

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources