Get results from a custom formula

I have a table which stores Allowance/Deductions codes and their standard amounts. The structure looks somewhat like this:

ADCode ADAmount
-
GIS 100
DPF 150
DPF.ADV 200
HRA 100

I want to give the user a capability to create any formula using column values and there by create a new ADCode depending upon other ADCodes, example:

New_ADCode_1 = (GIS + DPF + DPF.ADV)/20 * 100

New_ADCode_2 = 3 * (HRA)

Any idea on how to substitute values in the formula of the ADCodes that are present and create a new ADCode that uses this formula every time an employees pay is calculated.

I am using SQL Server 2005 with C#.How many ADCode do you have? If not many. You can simply SELECT them out through ADO.NET, cache them through Dictionary datastructure in C#. And use them whenever you want.

Because for formula caculation, .NET is much powerful than T-SQL.

Hope it'll be helpful.

|||

Here it is,

Code Snippet

Create Table data (

[Empid] int ,

[ADCode] Varchar(100) ,

[ADAmount] int

);

Insert Into data Values('1','GIS','100');

Insert Into data Values('1','DPF','150');

Insert Into data Values('1','DPF.ADV','200');

Insert Into data Values('1','HRA','100');

Insert Into data Values('2','GIS','100');

Insert Into data Values('2','DPF','150');

Insert Into data Values('2','DPF.ADV','200');

Insert Into data Values('2','HRA','100');

Approach 1:

Code Snippet

create function formula1(@.Empid int)

returns float

as

Begin

Declare @.result as float;

Select @.result = cast(Sum(AdAmount) as float)/20.0 * 100.0

From

data

Where

Adcode in('GIS','DPF','DPF.ADV')

and Empid = @.Empid;

return @.result;

End

create function formula2(@.Empid int)

returns float

as

Begin

Declare @.result as float;

Select @.result = 3 *AdAmount

From

data

Where

Adcode = 'HRA'

and Empid = @.Empid;

return @.result;

End

select *, dbo.formula1(empid), dbo.formula2(empid) from data

Approach 2:

Code Snippet

create function FormulaResults(@.Empid int)

returns table

as

Return

(

Select formula1 = cast(Sum(case when Adcode in('GIS','DPF','DPF.ADV') Then AdAmount End) as float)/20.0 * 100.0,

formula2 = cast(Sum(case when Adcode = ('HRA') Then AdAmount End) as float) * 3

From

data

Where

Empid = @.Empid

)

select *, formula1, formula2 from data d CROSS APPLYFormulaResults(d.empid) f

|||Manivannan,

Thanks for the solution. It seems a good one.

As you might be knowing, any Payroll system has Allowances and Deductions. Some Allowances and Deductions are applied directly, i.e., either they are added to Basic Pay or subtracted from Gross Pay. Some Allowances/Deductions have a special formula.

I want to know whether all calculations must reside as stored procedures/functions or they can be written entirely in a DLL.

|||

Frankly speaking about the application design, it is not good practice to overload your database with business logics, the new programming languages are much easier to create these functional requirements and rules. It is much easier to maintain also.