Calculated result sets

[pcf108]

I'm having a bit of design quandry... I need to return a result set of calculated values based upon multiple columns of data. Basically they are size calucations based on a table with Height, Width, and Thickness...

 

 

In T-SQL... (simplified, AdjustWidth involves CASE and gets complex)

 

SELECT 
[indent]Height, 
Width, 
Thickness, 
AdjustWidth = Width - 1, 
AdjustThickness = Thickness + 4, 
TotalWidth = 2*Width + Thickness + 2
TotalTimes8 = 16*Width + 8*Thicknes + 16[/indent]

TotalWidth depends on "AdjustWidth" but it cannot be reffered to directly because is not a column. I'd like to be able to do something like this, with a local variable

 

SELECT 
[indent]Height, 
Width, 
Thickness, 
@AdjustWidth = Width - 1, 
@AdjustThickness = Thickness + 4, 
@TotalWidth = 2*@AdjustWidth + @AdjustThickness
TotalTimes8 = 8*TotalWidth[/indent]

But this is not allowed in T-SQL. And I would need to return the variables in the result set.

 

I could write the calculation all inline like the first example but it gets incredibly confusing and longer the deeper I get (mostly due to CASE statements). I could write the adjustments as functions but then calling the same functions over and over.

 

I could build out a DataSet programmatically, but then I'd have some deployment issues. Any ideas on how to accomplish something like this on the back end?

 

I'm thinking about attempting to do this with CLR in 2005... Thoughts?

[Nerseus]

I've often put calculations in my SQL, if they're simple or straightforward. If there's lots of CASE logic, it's a toss-up - I may still keep it in SQL if it makes the most sense.

 

Two other options come to mind. First, expression columns in the dataset. Second, wrapping calls to your proc in a common C#/VB function that adds the columns programatically.

 

I might consider the expression columns first, because they're a bit easier to setup. But expression columns do have issues - the biggest being I don't believe you can do any "if" logic in them, at least not in .NET 1.x - I haven't looked into .NET 2.0.

 

The programatic solution seems the best in your case. What you describe is the need to get some base data out of the table, and from that base data get some new results, mostly calculated. That sounds like domain/business logic that's best put in the code that's easiest to understand and maintain.

 

-ner

[pcf108]

That's what I was thinking, it's just that this result set would be used for reports, exporting, & ODBC connectivity to machinery. A SQL solution would likey fit the bill better... I keep thinking CLR in 2005 would do the trick, but i have zero experience, plus it kinda freaks me out, microsoft overload.

 

I'll take a look at the expression columns though, sounds intruiging.

[Nerseus]

If this is meant to offload, you may be able to push for a 24-hour stale version of the data. Meaning, you'd build some kind of nightly batch job that calculated all your summary columns and put the results in a new table. Special reports (or data file extracts) would use this table to get the results. Clients would just have to agree that 24-hour stale data is Ok since you'd only want to take this hit once a day. I've implemented that kind of solution in the past. If your organization (or the client who's paying for the solution) can afford a stand alone reporting server that may even be better. That opens up a lot of possibilities, but comes at the cost of more hardware and possibly the complexity of a replication scheme. Just a few other ideas if you need a more data-centric approach where you won't be able to pull back data to some kind of C#/VB code for processing.

 

-ner

[pcf108]

Well, I'm not sure if that would work in this scenario... After somethought, I came up with a pretty simple T-SQL solution using a temp table and updates.

 

Putting the following in a stored procedure...

SELECT *, AdjustWidth = Width - 1, AdjustThickness = Thickness + 4, TotalWidth = Width
INTO #sizeResult
FROM Orders

UPDATE #sizeResult SET TotalWidth = 2*AdjustWidth + AdjustThickness

SELECT * FROM #sizeResult

 

I'm not sure that this method is efficient but it seemingly does the trick. The procedure could perform multiple selects into several temporary tables, but I'm guessing an UPDATE strategy would work better for more calculations. I didn't check performance. In addition, it might be possible to add columns to the temp table...

 

From what I can tell CLR would work too (perhaps with better performance). Building a SqlMetaData result set and populating a DataSet, performing the calculations, and sending the MetaData through the SqlPipe.

 

So, it looks like I have a few real-time options. Since I'm not working with 2005, I'll stick with T-SQL.

[Puiu]

I think you could also have something like:

 

declare @AdjustWidth int, @AdjustThickness int, @TotalWidth int

 

select @AdjustWidth = Width - 1 from Orders

select @AdjustThickness = Thickness + 4 from Orders

select @TotalWidth = 2*@AdjustWidth + @AdjustThickness

 

select Height, Width, Thickness, @AdjustWidth, @AdjustThickness, @TotalWidth, 8*@TotalWidth as TotalTimes8

from Orders

Edited May 10, 2006 by Puiu