Flexable Rate Recursion

,

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.

 

 

 

/*
 
 This recursion function was written to help load a table of rates for
 a number of employees. Each one had their own starting amount.
 Each one had their own set number of years available.
 Each one had their own expected yearly increase.
 
*/

/* ***** Object: UserDefinedFunction [dbo].[fn_RecurseM] Script Date: 2/13/2009 12:30 ******/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_RecurseM]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 DROP FUNCTION [dbo].[fn_RecurseM]
GO

/* ***** Object: UserDefinedFunction [dbo].[fn_RecurseM] Script Date: 2/13/2009 12:30 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[fn_RecurseM]
 (
 @Base decimal(32, 6),
 @Multiplier decimal(32, 6),
 @Times int
 )
 
 RETURNS decimal(32, 6)
 AS
 BEGIN

 DECLARE @Total decimal(32, 6)
 DECLARE @RLoop int
 DECLARE @SLoop int
 
 /*
 
 Clear and pre-set all internal variables.
 
 */
 
 SELECT
 @Total = 0.0,
 @RLoop = 0,
 @SLoop = 0
 
 /*
 
 Check if @Times is negative. This is an invalid value at this time.
 
 */
 
 IF @Times < 0
 BEGIN
 SELECT
 @Times = 0
 END
 
 /*
 
 Check if @Times is greater than 30.
 This is to keep the recursive function from
 erroring out due to too many recursions.
 Set up a secondary recursion loop if needed.
 
 */
 
 IF @Times > 30
 BEGIN
 SELECT
 @RLoop = ROUND((@Times / 30), 0, 1),
 @Times = (@Times % 30)
 SELECT
 @SLoop = @RLoop
 END
 
 /*
 
 This loop will be performed at least once.
 
 */
 
 WHILE @RLoop >= 0
 BEGIN
 
 /*
 
 Check if the recursion is split into multiple sets.
 Call the recursion function for each set. The full
 set will be completed before returning to the base call.
 
 */
 
 IF @Times > 0
 BEGIN
 SELECT
 @Total = dbo.fn_RecurseM(@Base, @Multiplier, (@Times - 1)) * @Multiplier
 
 /*
 
 Check if the total returned would exceed the highest value allowed when multiplied again.
 This check is needed within the recursion set loop. An invalid value is set to 0.0.
 This will keep potential errors from happening due to excessively large numbers.
 
 */
 
 IF (@Total * @Multiplier) > 999999999999999999999999.0
 BEGIN
 SELECT
 @Total = 0.0
 END
 
 /*
 
 Check if there is another recursion set loop.
 If so, then set the base to the current total. This will
 allow the recursion to continue properly.
 
 */
 
 IF @RLoop > 0
 BEGIN
 SELECT
 @Base = @Total
 END
 END
 ELSE
 
 /*
 
 This is the end of the recursion set. The base becomes the total as the
 first iteration needs the initial value as the first returned value.
 This value will be iteratively multiplied by the multiplier as each
 successive recursion returns back towards the root call.
 
 */
 
 BEGIN
 SELECT
 @Total = @Base
 END
 
 /*
 
 The end of the recursion set loop. Reduce the recursion set by 1.
 If another set is needed, then reset the recursion counter to 30.
 This is a hard-coded value as when this point is reached the first time,
 the less than 30 set has already been finished. When the recursion loop
 variable falls below 0 then the recursion set loop is done.
 
 */
 
 SELECT
 @RLoop = @RLoop - 1
 IF @RLoop >= 0
 BEGIN
 SELECT
 @Times = 30
 END
 END
 
 /*
 
 Return the new result after multiplied by the multiplier for the number of
 times needed.
 
 */
 
 RETURN(@Total)
 END
GO

Rate

1 (1)

Share

Share

Rate

1 (1)