From: richard.sherratt@NOTHINGHEREbrunsley.com.au   
      
   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.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)