Calculated filed from user formula

Question

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

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 25-Apr-21 18:21pm

Member 14561959

Updated 25-Apr-21 21:01pm

Maciej Los

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2021-04-25T21:01:00

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.