Use alias name as a column

• vigneshkumart50

  Ten Centuries

  Points: 1205

  More actions

  February 25, 2014 at 4:39 am

  #289878

  I'm using this query to to calculate yearly finance values.

  select [Year],[FinanceValue-2014],[FinanceValue-2013],[FinanceValue-2012],[FinanceValue- 2014]-[FinanceValue-2013] as [FinanceValue Variance]

  Now I need to multiply the [FinanceValue Variance] * 2.50 and for that how can I use the alias name as column in the query. I tried this but it says invalid column name.

  select [Year],[FinanceValue-2014],[FinanceValue-2013],[FinanceValue-2012],[FinanceValue- 2014]-[FinanceValue-2013] as [FinanceValue Variance], [FinanceValue Variance] * 2.50 as [NewVariance] from Finance

  SumofVariance output will be like 5690.5893656 Also how can I show the SumofVariance to round off 4 decimal places like this 5690.5894. Really appreciate any help on this.

• dbalmf

  SSCrazy

  Points: 2063

  More actions

  February 25, 2014 at 5:19 am

  #1691822

  Hi,

  something like this should work;

  select [Year],

  [FinanceValue-2014],

  [FinanceValue-2013],

  [FinanceValue-2012],

  'FinanceValue Variance' = [FinanceValue-2014]-[FinanceValue-2013],

  'SumofVariance' = CONVERT(MONEY,(([FinanceValue-2014]-[FinanceValue-2013])+[FinanceValue-2012]),2)

  from Finance

  Ta

  David

  ============================================================
  David

  http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

• below86

  SSChampion

  Points: 11532

  More actions

  February 26, 2014 at 7:47 am

  #1692283

  If you want to use the name of a calculated field I think the only way to do it would be to do a sub query. But for a simple calculation like this I would just put the calculation in the the new field name also.

  DECLARE @Finance TABLE

  (

  [Year] INT,

  [FinanceValue-2014] DECIMAL(12,2),

  [FinanceValue-2013] DECIMAL(12,2),

  [FinanceValue-2012] DECIMAL(12,2)

  )

  ;

  INSERT INTO @Finance

  SELECT 2014, 1000000, 900000, 800000;

  /* To do it your way. */

  SELECT [Year],

  [FinanceValue-2014],

  [FinanceValue-2013],

  [FinanceValue-2012],

  [FinanceValue Variance],

  CAST(([FinanceValue Variance] * 2.50) AS DECIMAL(12,4)) AS [NewVariance]

  FROM (SELECT [Year],

  [FinanceValue-2014],

  [FinanceValue-2013],

  [FinanceValue-2012],

  [FinanceValue-2014]-[FinanceValue-2013] AS [FinanceValue Variance]

  FROM @Finance) A

  ;

  /* Easier to just do it like this */

  SELECT [Year],

  [FinanceValue-2014],

  [FinanceValue-2013],

  [FinanceValue-2012],

  [FinanceValue-2014]-[FinanceValue-2013] AS [FinanceValue Variance],

  CAST((([FinanceValue-2014]-[FinanceValue-2013]) * 2.50) AS DECIMAL(12,4)) AS [NewVariance]

  FROM @Finance

  ;

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.