Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Flexable Rate Recursion

By Terry Steadman,

This function is used to take a constant rate and apply it to a base amount for a specified number of times and return the final adjusted amount. In my example, I had used a customer table with variable rates. This function can return either a final amount for each customer or it can be used recursively to create amount limit records that can then be inserted into a table.

As an example, the following code will create some test customer and rate tables with test data.

 

DECLARE @Customer table
(
Cust_ID int,
Cust_F_Name varchar(60),
Cust_M_Name varchar(40),
Cust_L_Name varchar(60),
Base_Limit decimal(9, 2)
)
DECLARE @RateSet table
(
Cust_ID int,
Annual_Rate decimal(8, 4),
Service_Years int
)
INSERT INTO @Customer
SELECT
1 AS Cust_ID,
'Joe' AS Cust_F_Name,
'Cig' AS Cust_M_Name,
'Camel' AS Cust_L_Name,
35540.00 AS Base_Limit
UNION
SELECT
2 AS Cust_ID,
'John' AS Cust_F_Name,
'Who' AS Cust_M_Name,
'Doe' AS Cust_L_Name,
19500.50 AS Base_Limit
UNION
SELECT
3 AS Cust_ID,
'Jane' AS Cust_F_Name,
'Who' AS Cust_M_Name,
'Doe' AS Cust_L_Name,
56560.49 AS Base_Limit
UNION
SELECT
4 AS Cust_ID,
'Mini' AS Cust_F_Name,
'Earner' AS Cust_M_Name,
'Wage' AS Cust_L_Name,
5.01 AS Base_Limit
INSERT INTO @RateSet
SELECT
1 AS Cust_ID,
1.0450 AS Annual_Rate,
12 AS Service_Years
UNION
SELECT
2 AS Cust_ID,
0.9412 AS Annual_Rate,
5 AS Service_Years
UNION
SELECT
3 AS Cust_ID,
1.2500 AS Annual_Rate,
18 AS Service_Years
UNION
SELECT
4 AS Cust_ID,
2.8021 AS Annual_Rate,
14 AS Service_Years

The following statement is all that is needed to find out what is the final rate amount for each customer as listed in the tables above. It will use the rate sets as defined so each customer can be processed separately and uniquely.

SELECT
cst.Cust_ID,
(cst.Cust_L_Name + ', ' + cst.Cust_F_Name + ', ' + cst.Cust_M_Name) AS Cust_Name,
cst.Base_Limit,
rts.Annual_Rate,
rts.Service_Years,
dbo.fn_RecurseM( cst.Base_Limit, rts.Annual_Rate, rts.Service_Years) AS Final_Limit
FROM @Customer AS cst
JOIN @RateSet AS rts ON
cst.Cust_ID = rts.Cust_ID

This same function can also be used to create an annual amount table that can then be used instead of recalculating the limit amount for each year.

;WITH
CustList AS
(
SELECT
cst.Cust_ID,
(cst.Cust_L_Name + ', ' + cst.Cust_F_Name + ', ' + cst.Cust_M_Name) AS Cust_Name,
cst.Base_Limit,
rts.Annual_Rate,
1 AS Proc_Year,
rts.Service_Years,
dbo.fn_RecurseM( cst.Base_Limit, rts.Annual_Rate, 1) AS Limit_Amount
FROM @Customer AS cst
JOIN @RateSet AS rts ON
cst.Cust_ID = rts.Cust_ID
UNION ALL
SELECT
cl.Cust_ID,
cl.Cust_Name,
cl.Base_Limit,
cl.Annual_Rate,
(cl.Proc_Year + 1) AS Proc_Year,
cl.Service_Years,
dbo.fn_RecurseM( cl.Base_Limit, cl.Annual_Rate, (cl.Proc_Year + 1)) AS Limit_Amount
FROM CustList AS cl
WHERE
cl.Proc_Year < cl.Service_Years
)
SELECT
Cust_ID,
Cust_Name,
Base_Limit,
Annual_Rate,
Proc_Year,
Service_Years,
CAST(Limit_Amount AS decimal(9, 2)) AS Limit_Amount
FROM CustList
ORDER BY
Cust_ID,
Proc_Year

 

The final SELECT statement can easily be turned into an INSERT statement to enter the records into a table. This can save a lot of work in creating the records and reduce possible entry errors.

 

 

 

Total article views: 534 | Views in the last 30 days: 4
 
Related Articles
FORUM

Limit buffer pool memory amount by database

Limit buffer pool memory amount by database

FORUM

Best way to select a large amount of data?

How to get large amount of data from a select without consumming all the server resources

FORUM

Customize SQL Query

Customize

FORUM

limit of data in select stored procedure

limit of data in one stored procedure

FORUM

Setting Report/Data Size limit in SSRS 2008

Limiting the amount of data a report can return

Tags
annual    
rates    
recursion    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones