Calculate formula VB.NET / SQL

Question

1.00/5 (1 vote)

See more:

i am using sql server and vb.net, I have following table
tblSalary
Name varchar(100)
Basic float
DARate Float
DA varchar(50)

data stored is as below
Name : John
Basic :100000
DARate : 30
DA :(Basic*DARate)/100

Please help me, how to execute formula ((Basic*DARate)/100) which is stored in DA field

thanks

What I have tried:

Calculate Formula vb.net / sql

Posted 5-Apr-16 21:18pm

Ranganath Deshpande

Updated 6-Apr-16 6:25am

v2

Add a Solution

Comments

Sergey Alexandrovich Kryukov 6-Apr-16 3:29am

All right, the formula is written. What's the problem? If you have a problem with that, how do you work with your database at all?
—SA

Ranganath Deshpande 6-Apr-16 9:58am

in the DA formula is stored as text, how to evaluate?

Sergey Alexandrovich Kryukov 6-Apr-16 11:10am

That was a bad idea. Evaluate where, in .NET? Quite possible (CodeDOM), but a huge, huge overkill, and not a reliable design. It's scratching your left ear with right foot. I would re-architecture the whole thing in much more sensible way. But first of all, why doing all that? What would be the ultimate goal?
—SA

Ranganath Deshpande 6-Apr-16 11:58am

for salary calculation, we have 2000 employees (employees are from different organisation working on deputation, we have to calculate according to their organisation), salary structure different for person to person, earning/deduction calculation is different. so i want to save the calculation in a field.
e.g DA is calculated generally Basic*DARate% , for some employees it is (Basic+GradePay)*DARate% , (Basic+SpecialPay)*DARate%
if i hardcode this in the code(VB.NET) then whenever new person joined i have to change my code each time.

Sergey Alexandrovich Kryukov 6-Apr-16 12:14pm

I say, storing a formula as text means extremely over-complicated, convoluted and unreliable approach.
I just consider counter-productive discussing it with you, even thought the problem is solvable. The mere fact that you don't realize the problem makes the prospects dull.

One solution is just one generalized formula and storing all its parameters in the database. Quite simple and safe.
Another option is stored procedure, with the calculations performed in SQL

—SA

Ranganath Deshpande 6-Apr-16 12:18pm

thanks for reply

Sergey Alexandrovich Kryukov 6-Apr-16 12:27pm

You are welcome. See also for my answer. If you leave the bad idea, you will see that the real solution is amazingly simple. See Solution 3.
—SA

3 solutions

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)

CHill60 · Answer 1 · 2016-04-06T02:55:00

Your table has

SQL

DA varchar(50)

This is a very bad design. DA is a number so this should be

SQL

DA float

Why? Because everytime you use it you are going to have to convert the results backwards and forwards from varchar to float. E.g.

SQL

UPDATE tblSalary SET DA = CAST((Basic*DARate)/100 AS VARCHAR(50))

If you absolutely insist on column DA being in the table (see my last comment below) then you could introduce a TRIGGER e.g.

SQL

CREATE TRIGGER tblSalary_AfterInsert 
	ON tblSalary AFTER INSERT
AS
  UPDATE tblSalary 
  SET DA = (tblSalary.[Basic] * tblSalary.DARate)/100
  FROM Inserted
  WHERE tblSalary.Name = Inserted.Name

Note - this assumes you have column DA as a float on the table. If you insist on leaving it as a varchar then you will need to change the SET line to

SET DA = CAST((Basic*DARate)/100 AS VARCHAR(50))

If it was me I wouldn't do any of the things above. DA can be calculated from other columns on the table so there is no need to store it. Furthermore if either [Basic] or DARate change then you also have to update the DA column - an unnecessary overhead. OR you have to change the trigger to also handle AfterUpdate... it's still unnecessary overhead.

I would just do the calculation when I need to ... right before the data gets passed back to the GUI (VB application):

SQL

SELECT [Name], [Basic], DARate, ([Basic]*DARate)/100 AS DA
FROM tblSalary

(Well my table schema would be very different but this gives you the idea)

[EDIT]
Points above are valid but not relevant. OP has now confirmed that the varchar column contains an expression that is to be parsed and evaluated.

These articles may help:
A Calculation Engine for .NET[^]
Implementing an Excel-like formula engine[^]
Jace.NET: Just Another Calculation Engine for .NET[^]

Gokulprasad05 · Answer 2 · 2016-04-05T21:28:00

Solution 1

select format(((column1*column2)/100),2) as alis_name from tablename;

Posted 5-Apr-16 21:28pm

Gokulprasad05

Sergey Alexandrovich Kryukov · Answer 3 · 2016-04-06T06:24:00

Please see my comments to the question.

I don't know if you can understand my explanation about how unacceptable and bad is the whole idea to store the formula.

Let me explain just the simplest possible solution. I can give you an example based on your three examples, so it's not final. The whole fact that you explain the situation by some example, but not by mathematically strict formulation, raise the concerns of your understanding. But I hope my example explains the idea.

Let's sat you have only those 3 options:
1) Basic*DARate; 2) (Basic+GradePay)*DARate% 3) (Basic+SpecialPay)*DARate.
If you look properly, you will see that this is not three, this is still one. Let's assume that you have three columns in some database table: Basic, DARate, GradePay and SpecialPay.

Then the formula is

(Basic + SpecialPay + GradePay) * DARate

For some customers, you simply store GragePay = 0, and SpecialPay = 0 and get formula #1.
For other, you store GragePay != 0, and SpecialPay = 0 and get formula #2;
You store GragePay = 0, and SpecialPay != 0 and get formula #3.

Moreover, any finite set of those (truly miserably trivial) cases can be generalized in one formula. No need to store it.

Isn't that simple?

Another, a very general option, is to use a stored procedure, to do all calculations purely on the database side.

—SA