locked
Calculate from Table Column in Sql server 2008 R2 RRS feed

  • Question

  • User-448512826 posted

    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 <g class="gr_ gr_56 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="56" data-gr-id="56">Bacis</g>
    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 <g class="gr_ gr_422 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="422" data-gr-id="422">dynamically</g> i.e from Formula column it should calculate.

    Regards

    and 

    Tuesday, February 5, 2019 11:22 AM

Answers

  • User-595703101 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 6, 2019 8:12 AM
  • User-595703101 posted

    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
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 8, 2019 11:02 AM

All replies

  • User-2082239438 posted

    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. 

    Wednesday, February 6, 2019 6:57 AM
  • User-448512826 posted

    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,

    Wednesday, February 6, 2019 7:39 AM
  • User-595703101 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 6, 2019 8:12 AM
  • User-448512826 posted

    hi <g class="gr_ gr_5 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="5" data-gr-id="5">eralper</g>,

    it <g class="gr_ gr_93 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="93" data-gr-id="93">throw</g> me <g class="gr_ gr_92 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="92" data-gr-id="92">a error</g> when <g class="gr_ gr_94 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="94" data-gr-id="94">i</g> execute this.

    error--Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the <g class="gr_ gr_103 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="103" data-gr-id="103">nvarchar</g> value '(' to data type int.

    execute like select *, dbo.replaceFormula(formula) from formulas 

    also when <g class="gr_ gr_127 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="127" data-gr-id="127">i</g> check with my own it <g class="gr_ gr_142 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="142" data-gr-id="142">throw</g> me <g class="gr_ gr_141 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="141" data-gr-id="141">error</g>

    Conversion failed when converting the <g class="gr_ gr_148 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="148" data-gr-id="148">nvarchar</g> value '*12%' to data type int.

    <g class="gr_ gr_205 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="205" data-gr-id="205">PAYID</g> 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 <g class="gr_ gr_211 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="211" data-gr-id="211">i</g> changed below <g class="gr_ gr_209 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="209" data-gr-id="209">line</g> as <g class="gr_ gr_208 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="208" data-gr-id="208">concat</g> not support in <g class="gr_ gr_206 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="206" data-gr-id="206">sqlserver</g> 2008

    set @newformula = @newformula+ @rate+ SUBSTRING(@tmp, @i+1, <g class="gr_ gr_207 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="207" data-gr-id="207">len</g>(@tmp)-@i+1)

    + replace with <g class="gr_ gr_1412 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="1412" data-gr-id="1412">concat</g>.

    Friday, February 8, 2019 2:41 AM
  • User-595703101 posted

    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

    Friday, February 8, 2019 5:50 AM
  • User-448512826 posted

    Hi Eralper,

    Thank you so much for your support.

    but <g class="gr_ gr_90 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="90" data-gr-id="90">i</g> need here the actual value <g class="gr_ gr_144 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="144" data-gr-id="144">not</g> 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,

    Friday, February 8, 2019 10:18 AM
  • User-595703101 posted

    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
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 8, 2019 11:02 AM
  • User-448512826 posted

    Hi,

    just confuse below <g class="gr_ gr_4 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" id="4" data-gr-id="4">line</g> 

    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,
    Friday, February 8, 2019 11:31 AM
  • User-595703101 posted

    % 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

    Friday, February 8, 2019 11:52 AM
  • User-448512826 posted

    ok..thanks<g class="gr_ gr_10 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation multiReplace" id="10" data-gr-id="10">..</g>

    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.  :)

    <g class="gr_ gr_398 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="398" data-gr-id="398">i</g> just confuse how to combine.. :)

    Thanks,

    Friday, February 8, 2019 12:11 PM