Use alias name as a column

  • 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.

  • 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]

  • 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.

Viewing 3 posts - 1 through 2 (of 2 total)

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