I have a stored procedure that's providing data to a different front
ends (Access, VB.Net and a Postgress-based web system) for reporting.
The report contains three money columns (annual, monthly and
pay-period) which have several levels of sub-totals and totals. So
that the various front-ends all present the same data (rounding can be
a problem), the sub-totals and totals are calculated in the SP. The SP
has one big Select statement to get the data. Is it possible to re-use
the calculated columns (sub-total) in other columns (totals) in the
select statement rather than repeating the calculations?

e.g.

Select A + B + C + D As column1,
E + F + G As column2,
column1 + column2 As column3,
H, I, J
From T

A, etc. are actually something like
Round(IsNull(fuel_annual, 0) / 12, 2)
but let's not complicate things too much :-)

At the moment I'm doing:

Select A + B + C + D As column1,
E + F + G As column2,
A + B + C + D + E + F + G As column3,
H, I, J
From T

I'd like to eliminate the double calculation.

I know that I could

Select @column1 = A + B + C + D,
@column2 = E + F + G
From T

and then

Select @column1 As column1, @column2 As column2,
@column1 + @column2 As column3,
H, I, J
From T

but then I've had two hits at the DB and I've probably done a few more
I/Os than I need to.

Any thoughts?



--
Regards.
Richard.

On Sep 20, 4:06*am, Richard Sherratt
<richard.sherr... (AT) NOTHINGHEREbrunsley (DOT) com.au> wrote:
You can't do this ...

------------------------------------------------------------------
Select A + B + C + D As column1,
E + F + G As column2,
column1 + column2 As column3,
H, I, J
From T
-------------------------------------------------------------------

.... because SQL does everything all at once, and not left-to-right.
That's actually a great advantage. The most efficient solution is
your own ...

----------------------------------------------------------------------
Select A + B + C + D As column1,
E + F + G As column2,
A + B + C + D + E + F + G As column3,
H, I, J
From T
-----------------------------------------------------------------------

.... but I know how messy that gets if you are using long functions, if
eg, "A" is a very long CASE statement. One other solution is a sub
query ...

-------------------------------------------------------------------------
Select x.column1, x.column2, x.column1 + x.column2 as column3
From (
select A+B+C+D as column1, E+F+G as column2
from T
) x
-------------------------------------------------------------------------

Of course, you are still taking two hits at the DB, but I don't think
you can avoid that.

On Mon, 20 Sep 2010 06:07:11 -0700 (PDT), Dom wrote:

(snip)
No, you are not. It's one round-trip, and the query optimizer will in
most cases compile an optimized plan that does all the work in a single
pass over the data.
This is, in fact, the best way to avoid repeating complex expression.
Especially if some of the expressions use subqueries, this will in most
cases perform better than repeating the expression.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Mon, 20 Sep 2010 23:22:56 +0200, Hugo Kornelis
<hugo (AT) perFact (DOT) REMOVETHIS.info.INVALID> wrote:

Ah ha!

Thanks, guys.

--
Regards.
Richard.