Click here to Skip to main content
14,976,254 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have sql server with two table
1. Account_chart
AccountID nvarcher(6)
Tax double

2. Finance_statement
Group nvarchar(10)
Formula nvarchar(50)
Taxes double

In the first table, i have data lake
201, 45
210, 52
345, 60
401000, 43

In the second table, i have
Immo, 20%+21%
Stock, 3%-39%
Fourn, 401%+44%


I want to evaluate formula.

What I have tried:

No idea, i don't know if i evaluate in vb.net or sqlserver.
Posted
Updated 25-Apr-21 21:01pm
v2
Comments
Member 14561959 26-Apr-21 16:47pm
   
I explain more
Immo 20%+21% the result must be :45+52=97
Stock 3%-39% the result must be : 60

20% that mean the accountid who have 20 in the begin ( in my exemple only 201)

Excuse me for not explaining more.

1 solution

Don't even think of doing it in SQL server!
It's possible - Evaluate a string Expression – SQLServerCentral[^] - but to use the "expressions" as you have them would take a fair amount of work, and be very messy, given SQL's poor string handling.

I'd read it into your presentation language, and work it out there: it shouldn't be too complex to do if they are all just
<value>%<operator><value>%
once you work out what relation the values have to your table data!

Me? I'd not use a string at all, but change the table definition.
If your expressions are always X% + Y% or X% - Y% then I'd have three columns:
XPercent   double
Sign       double
YPercent   double
Where sign is either 1.0 or -1.0, and the percentages are already divided by 100: .20, .21; .03, .39; 4.01, .44 and so on.

Then the calculation becomes trivial in either SQL or a presentation language:
(X * XPercent) + (Sign * Y * YPercent)
And is a lot easier to manage at a later date when a Y percentage needs to change.
   
Comments
Member 14561959 26-Apr-21 8:39am
   
Thank you for your reply, but % does mean "select * from accountid where accountid like '20%'"

Calculate alli accountid begin with 20.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900