 Calculate from Table Column in Sql server 2008 R2 [Answered]RSS

10 replies

Last post Feb 08, 2019 12:11 PM by salman behera

• Calculate from Table Column in Sql server 2008 R2

Feb 05, 2019 11:22 AM|salman behera|LINK

Hi,

here is my two table

 Id Name Rate 1 Basic 1000 2 HRA 200 3 PTO 100

 ID Formula Remark 1 (1+2-3)*12% 12 % of Basic+HRA-PTO 2 1*14% 14 % of Bacis 3 ((1+2)*(2+3))/100

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
• Re: Calculate from Table Column in Sql server 2008 R2

Feb 06, 2019 06:57 AM|yrb.yogi|LINK

salman behera

is there any solution?

can someone help me.

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.

• 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
• 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
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

I hope it helps

T-SQL programming SQL Server Tutorials
SQL Server 2017 • 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.

 PAYID PAYNAME TYPE FORMULA PARAMETER VALUE 1 BASIC LUM NULL parameter1 NULL 2 PF FOR {parameter1}*12% parameter2 NULL 3 HRA LUM NULL parameter3 NULL 4 ESI MAP 1;2 parameter4 10

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
• 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

convert(nvarchar(max), @rate)

like in

set @newformula = @newformula+ convert(nvarchar(max), @rate)+ SUBSTRING(@tmp, @i+1, len(@tmp)-@i+1)

The SQL CONCAT() function (in SQL Server 2012 and later) takes care of data type conversions

T-SQL programming SQL Server Tutorials
SQL Server 2017 • 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.

 Id Formula Remark (No column name) CORRECT VALUE 1 ({parameter1}+{parameter2}-{parameter3})*12% 12 % of Basic+HRA-PTO (1000+200-100)*12% 132 2 {parameter1}*14% 14 % of Bacis 1000*14% 140 3 (({parameter1}+{parameter2})*({parameter2}+{parameter3}))/100 NULL ((1000+200)*(200+100))/100 3600

i just pass FORMULA and RATE in that function and it should return me the Result only after calculation.

Thanks,

Sincerely,
Salman
• 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

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

T-SQL programming SQL Server Tutorials
SQL Server 2017 • Re: Calculate from Table Column in Sql server 2008 R2

Feb 08, 2019 11:31 AM|salman behera|LINK

Hi,

just confuse below line

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,
Sincerely,
Salman
• 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

T-SQL programming SQL Server Tutorials
SQL Server 2017 • 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,