SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 excel type logic		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pnpsql
Posting Yak Master

India
241 Posts
Posted - 07/08/2012 :  08:53:27  Show Profile  Reply with Quote
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

USA
933 Posts
Posted - 07/08/2012 :  12:53:20  Show Profile  Reply with Quote
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
Edited by - nathans on 07/08/2012 12:54:09
Go to Top of Page

pnpsql
Posting Yak Master

India
241 Posts
Posted - 07/09/2012 :  01:39:32  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 07/09/2012 :  09:52:21  Show Profile  Reply with Quote
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/
Go to Top of Page

nathans
Aged Yak Warrior

USA
933 Posts
Posted - 07/09/2012 :  13:50:06  Show Profile  Reply with Quote
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
Go to Top of Page

pnpsql
Posting Yak Master

India
241 Posts
Posted - 07/10/2012 :  00:35:15  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 07/10/2012 :  10:27:41  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000