9 replies

Last post Apr 04, 2016 08:36 AM by Weibo Zhang

All-Star

21483 Points

5962 Posts

Formula Converting value

Mar 28, 2016 12:25 PM|salman behera|LINK

Hi,

someone help me to create a function which return me to calculating value.

we developing a payroll application which we save the formula like below.

i need a function which return me the value after calculation.

pay component value like basic,HRA comes from different table like salay_master table and based on master data it return calculate value.

 FORMULA FORMULA CODE (BASIC+HOUSE RENT)*12/100 (1+2)*12/100 BASIC*12/100 1*12/100

if someone advice me better approach to calculate employee salary i will be happy.

Thanks...

Sincerely,
Salman

Star

7990 Points

1586 Posts

Re: Formula Converting value

Mar 29, 2016 02:15 AM|Weibo Zhang|LINK

Hi salman behera,

i need a function which return me the value after calculation.

pay component value like basic,HRA comes from different table like salay_master table and based on master data it return calculate value.

I have created the following function you could have a look.

```CREATE FUNCTION MyFunc20160329
(
@Basic decimal(10,2),
@HouseRent decimal(10,2)
)
RETURNS decimal(10,2)
AS
BEGIN
if @HouseRent is null
begin
set @HouseRent = 0
end
RETURN (@Basic+@HouseRent)*12/100
END
```

```declare @salay_master table (UserId int, Salay decimal(10,2))
insert into @salay_master values (1,6000),(2,7500)

declare @House_Rent table (UserId int, HouseRent decimal(10,2))
insert into @House_Rent values (1,123.4),(2,null)

select s.UserId, dbo.MyFunc20160329(s.Salay,h.HouseRent)
from @salay_master s, @House_Rent h
where s.UserId = h.UserId
```

Best Regards,

Weibo Zhang

Contributor

5774 Points

1170 Posts

Re: Formula Converting value

You can try below solution

```--exec dbo.ExecFormula '(BASIC+HRA)*12/100', 'EMP001'
alter procedure ExecFormula(@formula nvarchar(100), @empid varchar(100))
as
begin
declare @tab table (CompCode varchar(100), amount int, sno int)
insert into @tab values
('BASIC',10000, 1),('HRA',5000,2)--replace below query here
--select compcode, amount, row_number() over (order by CompCode) from salay_master where empid = @empid
declare @ctr int=0, @total int
select @total = count(1) from @tab
while @ctr<@total
begin
set @ctr = @ctr+1
select @formula = replace(@formula,CompCode,amount)  from @tab where sno=@ctr
end
print @formula
exec ('select ' + @formula)
end
```

Sandeep Mittal | Tech Blog : IT Developer Zone | Twitter : @itdeveloperzone | Facebook : @itdeveloperzone

All-Star

21483 Points

5962 Posts

Re: Formula Converting value

Mar 29, 2016 01:07 PM|salman behera|LINK

hi,

hope my requirement is  not clear here.

requirement is like below.

in  my master table i store data  like below format.

 ID NAME FORMULA FORMULACODE 1 BASIC 2 HRA (BASIC+HOUSE RENT)*12/100 (1+2)*12/100 3 PF BASIC*12/100 1*12/100

ID-1 for basic

ID-2 =HR

ID-3=PF

so i store formula in my master table for a particular pay head like above and my formula is like something below

(1+2)*12/100 for one payhead like HRA and 1*15/100(1 for basic) for another payhead like this PF

so when  i going to process salary from system ,then i need a function which calculating the formula by break it and return me the value.

for example for HRA payhead if my formula is  (1+2)*12/100 the 1 is basic and 2 is PF . basic value is 1000 and PF value is 400 from master TABLE then it return me

(1000+500)*12/100=180

and if i have 10 payhead in my master table then one by one one it calculate and insert in to the another table.

if no formula is there then it return me 0 or i fetch lumsum amount from master table.

hope it clear.

Thanks...

Sincerely,
Salman

Star

7990 Points

1586 Posts

Re: Formula Converting value

Mar 30, 2016 08:22 AM|Weibo Zhang|LINK

Hi salman behera,

for example for HRA payhead if my formula is  (1+2)*12/100 the 1 is basic and 2 is PF . basic value is 1000 and PF value is 400 from master TABLE then it return me

(1000+500)*12/100=180

According to your above description, I guess the “1” AND “2” in “HRA payhead” represent the two parameters, like C# function. If I’m wrong, please let me know.

If true, I suggest you’d better replace the “1” and “2” with some special placeholders, such as “\$1” and “\$2”, in your table to define the Computation Rules.

For this, I suggest you’d better implement this complex and flexible operations in your C# or VB.Net code, not in the SQL side.

As I searched and try, I built below method to try achieving the need. But, when I call it by using “select dbo.MyFunc20160330('(\$1+\$2)*12/100',1000,400)”, it returns the error message “Only functions and some extended stored procedures can be executed from within a function.” As I searched, the Custom Function is not allowed to run dynamically SQL string by design. For this, you could have a look at below threads. So, this method is not the solution.

http://dba.stackexchange.com/questions/94042/error-with-function-sql-server-2008-cannot-find-either-column

```alter FUNCTION MyFunc20160330
(
@FormulaStr varchar(max),
@Basic decimal(10,2),
@HouseRent decimal(10,2)
)
RETURNS varchar(20)
AS
BEGIN
if @HouseRent is null
begin
set @HouseRent = 0
end

set @FormulaStr = REPLACE(REPLACE(@FormulaStr,'\$1',@Basic),'\$2',@HouseRent)

declare @sql nvarchar(max)
set @sql = 'select @result = ' + @FormulaStr

declare @result varchar(20)
exec sp_executesql @sql, N'@result int output', @result = @result out

RETURN @result
END
```

After searching, I think you could use Store Procedure to run the dynamically sql string as below.

```CREATE PROCEDURE [dbo].[MyProc20160330]
(
@FormulaStr varchar(max),
@Basic decimal(10,2),
@HouseRent decimal(10,2)
)
AS
Begin
if @HouseRent is null
begin
set @HouseRent = 0
end

set @FormulaStr = REPLACE(REPLACE(@FormulaStr,'\$1',@Basic),'\$2',@HouseRent)

declare @sql nvarchar(max)
set @sql = 'select @result = ' + @FormulaStr

declare @result decimal(10,2)
exec sp_executesql @sql, N'@result decimal(10,2) output', @result = @result out

return @result

End```

Then, you could use below code to get the return value

```declare @res decimal(10,2)
exec @res= dbo.[MyProc20160330] N'(\$1+\$2)*12/100',1000,400
select @res
```

For your need, you could try to create a SP and pass the table to it to handle get the result by using TVP, but I still think using C# or VB to do it would be better and easy.

https://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/

Best Regards,

Weibo Zhang

All-Star

21483 Points

5962 Posts

Re: Formula Converting value

Mar 31, 2016 07:09 AM|salman behera|LINK

Hi Weibo Zhang,

thanks for your effort. already i have done it using code behind(C#) and it working fine.

i created a method in c# and pass Formula column value and empid into that method then it return me the value.

but problem is ,i need to the same in backend like sql server.

i know it is possible, so need someone's help.

Thanks...

Sincerely,
Salman

Star

7990 Points

1586 Posts

Re: Formula Converting value

Mar 31, 2016 10:19 AM|Weibo Zhang|LINK

Hi salman behera,

I have finished the whole code as below and you could have a look. In the code, I use the above SP and you could pay attention to it.

1.Create custom Table Type as blow

```CREATE TYPE [dbo].[TVP_FORMULA] AS TABLE (
[ID]          INT          NOT NULL,
[Name]        VARCHAR (20) NOT NULL,
[FORMULA]     VARCHAR (50) NULL,
[FORMULACODE] VARCHAR (50) NULL);
```

2. Create the Store Procedure to execute the TVP and my above SP “dbo.[MyProc20160330]”

```CREATE PROCEDURE [dbo].[MyProc20160331]
(
@Basic decimal(10,2),
@HouseRent decimal(10,2)
)
AS
Begin
--Store the final result
declare @Result table (Id int,FORMULANAME VARCHAR(20), FORMULA VARCHAR(50),FORMULACODE VARCHAR(50),VALUE decimal(10,2))

declare @rowNum int = 0,--As a cursor to get each row values from TVP
@totalCount int
select @totalCount = COUNT(*) from @TVP_Val --Get the row count of TVP

while
@rowNum < @totalCount
Begin
declare @Id int
declare @FORMULANAME VARCHAR(20)
declare @FORMULA VARCHAR(50)
--Get the FORMULACODE from the TVP'@TVP_Val'
declare @FORMULACODE varchar(50)
select
@Id= id,
@FORMULANAME = NAME,
@FORMULA=FORMULA,
@FORMULACODE = FORMULACODE
from @TVP_Val where ID = @rowNum+1

--Get the return value of SP'[MyProc20160330]'
declare @res decimal(10,2)
if @FORMULACODE is not null and @FORMULACODE <> ''
begin
exec @res= dbo.[MyProc20160330] @FORMULACODE,@Basic,@HouseRent
end
else
begin
set @res= null
end

--Add the result into final result
insert into @Result values (@Id,@FORMULANAME,@FORMULA,@FORMULACODE,@res)
set @rowNum = @rowNum+1
End

select * from @Result

End
```

3.Call the [dbo].[MyProc20160331]

```declare @TVP_Val dbo.[TVP_FORMULA]
insert into @TVP_Val values (1, 'BASIC','',''),
(2 ,'HRA', '(BASIC+HOUSE RENT)*12/100', '(\$1+\$2)*12/100'),
(3 ,'PF', 'BASIC*12/100' ,'\$1*12/100')

declare @Basic decimal(10,2) = 1000,@HouseRent decimal(10,2)=400

exec [dbo].[MyProc20160331] @TVP_Val,@Basic,@HouseRent
```

4.The result is  below

```Id	FORMULANAME	FORMULA	FORMULACODE	VALUE
1	BASIC			NULL
2	HRA	(BASIC+HOUSE RENT)*12/100	(\$1+\$2)*12/100	168.00
3	PF	BASIC*12/100	\$1*12/100	120.00```

Best Regards,

Weibo Zhang

All-Star

21483 Points

5962 Posts

Re: Formula Converting value

Apr 01, 2016 04:45 AM|salman behera|LINK

Hi Weibo Zhang,

Thanks for your effort.but it is not met my requirement.

Basic,HRA,PF.. etc can not be a parameter in procedure.it should be from master table.

my requrement is just pass formulacode and EmployeeID  and based on value of paycomponent like Basic,PF,HRA from master table(Salary master) against each employee,it return the value.

Salary master table like

Paycode     EmpID           Value

1 (Baisc)                    1                  1000

1                            2                         3000

if it possible??

here my c# code which working fine..

``` public string GetFormulaValue(String formula, int empId)
{
string tot = "";

string firsthalf = string.Empty;
string secondhalf = string.Empty;

//String testData = "(1+2-4)*12/100";
String testData = formula;
//break only pay element here
String[] firststhalf = testData.Split(new Char[] { ')' });

//assign it in a different string
firsthalf = firststhalf[0].Substring(1);
secondhalf = firststhalf[1];

var lMo = new List<MathOper>();
int l = firsthalf.Length;//5
for (int i = 0; i < l; i = i + 2)
{
char n = firsthalf[i];
int n1 = int.Parse(n.ToString(CultureInfo.InvariantCulture));
string o1 = string.Empty;
if (i > 0)
{
o1 = firsthalf[i - 1].ToString(CultureInfo.InvariantCulture);
}
var mo = new MathOper { number = n1, Opertor = o1 };
}
// return lMo;
for (int k = 0; k < lMo.ToList().Count; k++)
{
string value = lMo[k].number.ToString();
string opert = lMo[k].Opertor.ToString();
//sqlquery
string fetch = FetchPayAmountByID(Convert.ToInt32(value), empId);
if (!string.IsNullOrEmpty(opert))
{
tot += opert + fetch;
}
else
{
tot = fetch;
}
}
string finalstring = "(" + tot + ")" + secondhalf;
var loDataTable = new DataTable();
var loDataColumn = new DataColumn("Eval", typeof(double), finalstring);
return loDataTable.Rows[0]["Eval"].ToString();

}```

hope you understood.

Thanks..

Sincerely,
Salman

All-Star

21483 Points

5962 Posts

Re: Formula Converting value

Apr 04, 2016 06:17 AM|salman behera|LINK

Hi,

is there any solution??

is there any better idea for resolve the same problem using sql server.

Regards,

salman

Sincerely,
Salman

Star

7990 Points

1586 Posts

Re: Formula Converting value

Apr 04, 2016 08:36 AM|Weibo Zhang|LINK

Hi salman behera,

salman behera

Basic,HRA,PF.. etc can not be a parameter in procedure.it should be from master table.

my requrement is just pass formulacode and EmployeeID  and based on value of paycomponent like Basic,PF,HRA from master table(Salary master) against each employee,it return the value.

If the basic and HRA are not the parameters of stored procedure, how you pass the corresponding values for each employee?

If you want to calculate multiple records, you could first get the corresponding information to a datatable and then loop through all the rows to call my above SP to achieve your goal. When you call the above SP, you should first declare a table as the formulas out of this circulation and then get the basic and HRA values from each row. I suggest you’d better try it by yourself and modify it according to your need.

If you have any issue about how to implement this part, it would be a new issue in this thread and you’d better post them in a new thread for better support. Thank you for your support and understanding.

Best Regards,

Weibo Zhang