Literal Calculations Stored in Table

Question

Post reply

Literal Calculations Stored in Table

Stephen Stoyko

SSC Rookie

Points: 26
More actions
March 28, 2006 at 5:59 am

#112535

Is it possible to store literal calculations in a table instead of writing an extremely long Stored Procedure?
Currently I have two tables, Metric & MetricResults:
Metric
ID        MetricName
1          Monthly Sales
2          Budget
3          Returns

MetricResults (input)
Year     Month MetricID          Result
2006    1            1                      5000
2006    1            2                      4500
2006    1            3                      300

I'd like to add a third table that controls the output.
Calculations
Element                  Calculation
Sales vs. Budget      <<MetricID.1>> - <<MetricID.2>>
Returns to Sales      <<MetricID.3>> / <<MetricID.1>>

In a perfect world, I'd execute a SP and get back the following:

Sales vs. Budget     500
Returns to Sales     6%

When all is said and done, I'll have 30+ inputs and 50+ calculations.

Any help you can offer is greatly appreciated.

Viewing 10 posts - 1 through 9 (of 9 total)

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

RyanRandall SSChampion Points: 13623 More actions · Answer 1

Hi Stephen,

No, that's not really possible - well, not unless you're striving for a performance nightmare

In my opinion you don't have the best initial data structure (regardless of the other inputs and calculations - I know you have loads more). Your result column is storing values which don't really relate to the same thing (sales, budget etc), and that's not advisable.

If I were you, I'd change my initial data structure and create calculations based around that. So - in your example - I'd do something like this...

declare @month table (Year int, Month int, Sales money, Budget money, Returns money)

insert @month values (2006, 1, 5000, 4500, 300)

select

Sales - Budget as 'Sales vs. Budget',

Returns / Sales as 'Returns to Sales '

from

@month

I know that's not the answer you were looking for, so sorry.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 2

Hi,

You can use the View to solve Your Problem::

Create View Vw_Calculations

as

select (select Result from metricResults where metricid=1) - (select Result from metricResults where metricid=2) 'Sale-Budget',

(select Result from metricResults where metricid=3) * 100 / (select Result from metricResults where metricid=1) 'Retun To Sale'

when you run the :

select * from Vw_Calculations

it will provide to you the desired result.

Regards,

Amit GUPTA

RyanRandall SSChampion Points: 13623 More actions · Answer 3

That doesn't answer the question he was asking, Amit.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Steve Jones - SSC Editor SSC Guru Points: 737004 More actions · Answer 4

Write the stored proc. You'll do it once and it will work. Even if you need to write 50 procs, it's small compared to the issues you might face with computed columns

RyanRandall SSChampion Points: 13623 More actions · Answer 5

Steve - wouldn't you want to sort out that non-normalised data structure before you spent ages writing a stored procedure based on it? (As I mentioned above... )

I know it might not be possible, but that's what I'd "want" to do.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Marshall Smith SSCrazy Points: 2041 More actions · Answer 6

Just for the record, it might be possible. I know that I can store VB code in a table, and execute it (don't ask, it was a complicated situation where business rules changed based on the data). You can use EXEC to execute the contents of a string. However, passing the string to the EXEC command can be tricky. I don't know if it's possible in T-SQL.

RyanRandall SSChampion Points: 13623 More actions · Answer 7

It is possible - just not a good idea.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Stephen Stoyko SSC Rookie Points: 26 More actions · Answer 8

Thanks for all your posts.

I did find a way to store the calculation and parse it in the SP, but was unable to execute it.

I ran out of time, so I ended up just performing each calc in the SP.

Ryan, you questioned my data structure and I'm a little confused. If I would have made the structure as you suggest, each time a new metric was added, all the SP would need to be updated. This seems to be much more effort then the data structure we laid out. Am I missing something?

Marshall Smith SSCrazy Points: 2041 More actions · Answer 9

How often do you add new metrics? If it's two or three times a month, then, yeah, you have a need for a dynamic system. If it's once every six months or less, then you're probably better off saving the metrics themselves as stored procedures (or, probably even better, UDFs).

So, make a UDF called SalesVsBudget. Define how it is supposed to calculate values. Then, call it in other queries where needed.

However, I'm going to reiterate Ryan's point. I think you have a seriously non-normalized structure there. Obviously, we're only seeing a small snapshot into your schema. But, you may want to take a long step back, and re-evaluate how you're storing the data.