I want a function which calculates from Formula Column of second table and value fetch from the 1st table like 1=1000,2=200 like this and return the calculated Value in SQL server 2008 R2.
all should be dynamically i.e from Formula column it should calculate.
Regards
and
"…Mark As Answer" if my reply helpful to you…”
Sincerely,
Salman
payment not fix.it is dynamic.so as per requirement there is one master table which store Rate as per PayId and there is another table which store the calculation type i.e Fixed or Calculated then if calculated then based on a formula it should calculate
and return calculated value.
Regards,
"…Mark As Answer" if my reply helpful to you…”
Sincerely,
Salman
I created a UDF function that splits formula and fetches parameters name then replaces parameters with their values as follows
create or alter function replaceFormula (
@formula nvarchar(max)
) returns nvarchar(max)
begin
declare @i int
declare @rate int
declare @parameter varchar(50)
declare @newformula nvarchar(max) = ''
declare @tmp nvarchar(max) = @formula
set @i = PATINDEX('%{parameter%', @tmp)
while @i > 0
begin
set @newformula = left(@tmp, @i-1)
set @tmp = SUBSTRING(@tmp, @i, len(@tmp)-@i+1)
set @i = CharIndex('}', @tmp)
set @parameter = SUBSTRING(@tmp,1,@i)
set @parameter = SUBSTRING(@parameter, 2, len(@parameter) - 2)
select @rate = rate from aspnet.formula_parameters where id = @parameter
set @newformula = CONCAT(@newformula, @rate, SUBSTRING(@tmp, @i+1, len(@tmp)-@i+1) )
set @tmp = @newformula
set @i = PATINDEX('%{parameter%', @tmp)
end
return @newformula
end
go
Please note, I designed this solution for following conditions
Parameter are like 'parameter1', 'parameter2'
In formulas parameters are used with curly brackets aroung like {parameter1}*{parameter2}
Please check following sample data as well
create table aspnet.formula_parameters (
Id varchar(30) not null primary key,
Name nvarchar(100) not null,
Rate int not null
)
create table aspnet.formulas (
Id int not null primary key,
Formula nvarchar(1000) not null,
Remark nvarchar(1000)
)
insert into aspnet.formulas
select 1,'({parameter1}+{parameter2}-{parameter3})*12%', '12 % of Basic+HRA-PTO'
insert into aspnet.formulas
select 2,'{parameter1}*14%', '14 % of Bacis'
insert into aspnet.formulas
select 3,'(({parameter1}+{parameter2})*({parameter2}+{parameter3}))/100', null
insert into aspnet.formula_parameters select 'parameter1','Basic',1000
insert into aspnet.formula_parameters select 'parameter2','HRA',200
insert into aspnet.formula_parameters select 'parameter3','PTO',100
Then below SQL can be used to query formula data with figures
select *, dbo.replaceFormula(formula) from aspnet.formulas
OK :) Since we cannot use SP_EXECUTESQL etc to evaluate string expression into a numeric value within UDF functions, I prepared following
SQL cursor code for you
drop table if exists ##formula
select *, dbo.replaceFormula(formula) as f, NULL as val
into ##formula
from aspnet.formulas where 1 = 0
DECLARE @Id Int
DECLARE @Val Int
Declare @formula nvarchar(max)
declare @sql nvarchar(max)
DECLARE formula_cursor CURSOR FAST_FORWARD FOR
select Id, dbo.replaceFormula(formula) from aspnet.formulas
OPEN formula_cursor
FETCH NEXT FROM formula_cursor INTO @Id, @formula
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'SELECT @val = ' + convert(nvarchar(max), replace(@formula,'%','*1.0/100'))
EXECUTE sp_executesql @sql, N'@val int OUTPUT', @Val = @Val OUTPUT
print @Val
insert into ##formula select *, @formula, @Val from aspnet.formulas where Id = @Id
FETCH NEXT FROM formula_cursor INTO @Id, @formula
END
CLOSE formula_cursor
DEALLOCATE formula_cursor
select * from ##formula
set@sql ='SELECT @val = '+convert(nvarchar(max), replace(@formula,'%','*1.0/100')) it replace formula with '%','*1.0/100' but how can i do it dynamically like any operator i.e /,{,} etc. can you please clear me .
or is there any simple way or is it not possible with one function? Thanks,
"…Mark As Answer" if my reply helpful to you…”
Sincerely,
Salman
All-Star
21479 Points
5971 Posts
Calculate from Table Column in Sql server 2008 R2
Feb 05, 2019 11:22 AM|salman behera|LINK
Hi,
here is my two table
I want a function which calculates from Formula Column of second table and value fetch from the 1st table like 1=1000,2=200 like this and return the calculated Value in SQL server 2008 R2.
all should be dynamically i.e from Formula column it should calculate.
Regards
and
Sincerely,
Salman
Star
10293 Points
2358 Posts
Re: Calculate from Table Column in Sql server 2008 R2
Feb 06, 2019 06:57 AM|yrb.yogi|LINK
You will need to re-structure your table. As per the current table structure, it makes difficult to define any SQL query.
As per my observation, if you will have only three payment/formula head then you will need to define the case by case logic in the SQL.
Sr Technical Lead
Ahmedabad, India
All-Star
21479 Points
5971 Posts
Re: Calculate from Table Column in Sql server 2008 R2
Feb 06, 2019 07:39 AM|salman behera|LINK
Hi,
payment not fix.it is dynamic.so as per requirement there is one master table which store Rate as per PayId and there is another table which store the calculation type i.e Fixed or Calculated then if calculated then based on a formula it should calculate and return calculated value.
Regards,
Sincerely,
Salman
Contributor
6081 Points
1445 Posts
Re: Calculate from Table Column in Sql server 2008 R2
Feb 06, 2019 08:12 AM|eralper|LINK
I created a UDF function that splits formula and fetches parameters name then replaces parameters with their values as follows
create or alter function replaceFormula ( @formula nvarchar(max) ) returns nvarchar(max) begin declare @i int declare @rate int declare @parameter varchar(50) declare @newformula nvarchar(max) = '' declare @tmp nvarchar(max) = @formula set @i = PATINDEX('%{parameter%', @tmp) while @i > 0 begin set @newformula = left(@tmp, @i-1) set @tmp = SUBSTRING(@tmp, @i, len(@tmp)-@i+1) set @i = CharIndex('}', @tmp) set @parameter = SUBSTRING(@tmp,1,@i) set @parameter = SUBSTRING(@parameter, 2, len(@parameter) - 2) select @rate = rate from aspnet.formula_parameters where id = @parameter set @newformula = CONCAT(@newformula, @rate, SUBSTRING(@tmp, @i+1, len(@tmp)-@i+1) ) set @tmp = @newformula set @i = PATINDEX('%{parameter%', @tmp) end return @newformula end goPlease note, I designed this solution for following conditions
Parameter are like 'parameter1', 'parameter2'
In formulas parameters are used with curly brackets aroung like {parameter1}*{parameter2}
Please check following sample data as well
create table aspnet.formula_parameters ( Id varchar(30) not null primary key, Name nvarchar(100) not null, Rate int not null ) create table aspnet.formulas ( Id int not null primary key, Formula nvarchar(1000) not null, Remark nvarchar(1000) ) insert into aspnet.formulas select 1,'({parameter1}+{parameter2}-{parameter3})*12%', '12 % of Basic+HRA-PTO' insert into aspnet.formulas select 2,'{parameter1}*14%', '14 % of Bacis' insert into aspnet.formulas select 3,'(({parameter1}+{parameter2})*({parameter2}+{parameter3}))/100', null insert into aspnet.formula_parameters select 'parameter1','Basic',1000 insert into aspnet.formula_parameters select 'parameter2','HRA',200 insert into aspnet.formula_parameters select 'parameter3','PTO',100Then below SQL can be used to query formula data with figures
I hope it helps
SQL Server 2017
All-Star
21479 Points
5971 Posts
Re: Calculate from Table Column in Sql server 2008 R2
Feb 08, 2019 02:41 AM|salman behera|LINK
hi eralper,
it throw me a error when i execute this.
error--Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '(' to data type int.
execute like select *, dbo.replaceFormula(formula) from formulas
also when i check with my own it throw me error
Conversion failed when converting the nvarchar value '*12%' to data type int.
just test me out and yes i changed below line as concat not support in sqlserver 2008
set @newformula = @newformula+ @rate+ SUBSTRING(@tmp, @i+1, len(@tmp)-@i+1)
+ replace with concat.
Sincerely,
Salman
Contributor
6081 Points
1445 Posts
Re: Calculate from Table Column in Sql server 2008 R2
Feb 08, 2019 05:50 AM|eralper|LINK
Yes, that is because when you are using " + " to concatenate a character value with a numeric value
Please, convert numeric values before using them in a " + " concatenation operation as follows
like in
The SQL CONCAT() function (in SQL Server 2012 and later) takes care of data type conversions
SQL Server 2017
All-Star
21479 Points
5971 Posts
Re: Calculate from Table Column in Sql server 2008 R2
Feb 08, 2019 10:18 AM|salman behera|LINK
Hi Eralper,
Thank you so much for your support.
but i need here the actual value not FORMULA itself.
i just pass FORMULA and RATE in that function and it should return me the Result only after calculation.
Thanks,
Sincerely,
Salman
Contributor
6081 Points
1445 Posts
Re: Calculate from Table Column in Sql server 2008 R2
Feb 08, 2019 11:02 AM|eralper|LINK
OK :) Since we cannot use SP_EXECUTESQL etc to evaluate string expression into a numeric value within UDF functions, I prepared following SQL cursor code for you
You will see I use sp_executesql with output parameter definitions that calculates the numeric value of the formula
Please check if it helps for you
SQL Server 2017
All-Star
21479 Points
5971 Posts
Re: Calculate from Table Column in Sql server 2008 R2
Feb 08, 2019 11:31 AM|salman behera|LINK
Hi,
just confuse below line
Sincerely,
Salman
Contributor
6081 Points
1445 Posts
Re: Calculate from Table Column in Sql server 2008 R2
Feb 08, 2019 11:52 AM|eralper|LINK
% is not used as the percentage operator in SQL Server, it is for modulo calculation (for arithmetic operators please refer to link)
So I had to replace it
Somehow we have to replace or convert human understandable formula to SQL engine language :)
Do you have other types of calculations? Maybe we can check
SQL Server 2017
All-Star
21479 Points
5971 Posts
Re: Calculate from Table Column in Sql server 2008 R2
Feb 08, 2019 12:11 PM|salman behera|LINK
ok..thanks..
can you please combine function and cursor in one as another function or any other else.
just pass two parameters like formula and Rate and return calculated value. :)
i just confuse how to combine.. :)
Thanks,
Sincerely,
Salman