excel type logic - 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

General SQL Server Forums

New to SQL Server Programming

excel type logic

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-07-08 : 08:53:27

i have created a schedule in excel like below

there is formula in no col like : prin * rate / 80;

and in bal column from second row : bal - no

prin 100
rate 18

id no bal
1 22.5 100
2 22.5 77.5
3 22.5 55
4 22.5 32.5
5 22.5 10
6 22.5 -12.5
7 22.5 -35

after that i need to do that take a value in rate field that leads the bal column last row value to 0.00 and for the same i use goal seek in excel. and the result is shown below .

prin 100
rate 13.33333333

id no bal
1 16.66666667 100
2 16.66666667 83.33333333
3 16.66666667 66.66666667
4 16.66666667 50
5 16.66666667 33.33333333
6 16.66666667 16.66666667
7 16.66666667 0.00

but now i use to do the same in tsql.

pls help

challenge everything

nathans
Aged Yak Warrior

938 Posts

Posted - 2012-07-08 : 12:53:20

How about



declare @prin decimal(10,2) = 100,
	@rate decimal(10,8) = 18.0--13.33333333
		
		
;with numbers (n)--replace with your num table
as	(	select n from (values(1),(2),(3),(4),(5),(6),(7))v(n)
	)
select	[Id] = n, 
	[no] = (@prin*@rate)/80.0,
	[bal] = @prin - ((@prin*@rate)*(n-1))/80.0
from	numbers

Nathan Skerl

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-07-09 : 01:39:32

i need to pass number of rows that should be deisplayed and last row should be 0.00 and
according to that the @rate need to change.

challenge everything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 09:52:21

hmm...do you mean to say @rate is not an input? so what all user will input as values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nathans
Aged Yak Warrior

938 Posts

Posted - 2012-07-09 : 13:50:06

Yes, I thought prin and rate were your inputs. If you would also like to input the "number of rows" just use it to limit the numbers table, ie:


...
from	numbers
where   n <= @NumberOfRows

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-07-10 : 00:35:15

the int rate, prin and no of rows are inputs but the intrate should change when we set last row as o.oo
.

challenge everything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-10 : 10:27:41

sorry your scenario is not clear. please post sample data as below for us to help you further

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources