How to use table column values as such for calculations?

Question

Post reply

How to use table column values as such for calculations?

nivecse

SSC-Addicted

Points: 409
More actions
May 7, 2012 at 3:51 pm

#150937

Hi,
Am using SQL Server 2005. I have 2 tables.
Table A (RuleNo, RuleDesc)
Table B (Col1,Col2,Col3).
Table C (Col1,Col2)
Table A has values as below:
RuleNo RuleDesc
----------------
1 Col1*Col2/Col3
2 Col2*Col3
3 Col1*100/Col2
Where RuleDesc column contains the rule for calculation based on the values from Table B. Lets assume Table B has values like below:
Col1 Col2 Col3 Col4
---------------------
1 2 2 S1
2 4 3 S3
4 1 5 S2
Table C has to apply the rule from Table A as below:
Col1 Col2
-----------
S1 1
S2 3
S3 2
Col2 of Table C refers to the RuleNo of Table A. For eg, S1 refers to RuleNo 1 of Table A. When the RuleNo 1 (Col1*Col2/Col3) is applied with the values from table B, S1 will have value as 1. Like wise, my table A has lots of rules to be applied for Table C.
How to calculate this value dynamically using the formula from TableA?Is there any possibility of doing this in sql server tables?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

It's possible with use of dynamic SQL

BUT! It's a crazy desire!

p.s. you can draw using T-SQL too...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

This topic comes up around here from time to time. Pretty much everytime the OP decides to go back to the drawing board because the complexity of this is crazy. The other option is to do this in the front end. SQL is really good at storing and retrieving data. It just wasn't designed for this type of thing. This goes WAY beyond data retrieval and is full into a programming language (which t-sql is not).

That being said it CAN be done with dynamic sql but it will probably run pretty poorly. You would be far better off storing your calculated values instead of storing formulas for how to calculate it later.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/