Use a CASE WHEN in the same SELECT command?

Question

A simple Select x, y , z from Table query is containing a "Case when" in the select.

This field is ending as "End as 'result field'".

I would like to use that 'result field' in another command as part of the same SELECT command (a Dateadd command).

SELECT
    PositionDate,
    ISIN,
    Issuer,
    FirstCouponDate,
    TermLength,
    TermUnit,
    CASE
       WHEN TermUnit = 'Years' THEN 'year'
       WHEN TermUnit = 'Months' THEN 'month'
    END AS 'TermUnitRenamed', 
    DATEADD(TermUnitRenamed, TermLength, FirstCouponDate),
FROM
    GIBDataWarehouse.dbo.vw_Positions

Answer 1

Two suggestions based on the information you provided: add an ELSE default value to your CASE statement; then, nest this query inside a subsequent one so you can reference your new created field.

select
    *
    , case 
        when TermUnitRenamed = 'year' then DateAdd(year, TermLength, FirstCouponDate) 
        when TermUnitRenamed = 'month' then DateAdd(month, TermLength, FirstCouponDate) 
        when TermUnitRenamed = 'day' then DateAdd(day, TermLength, FirstCouponDate) 
        end as newDate
    from (
        select
            PositionDate
            , ISIN
            , Issuer
            , FirstCouponDate
            , TermLength
            , TermUnit
            , case
                when TermUnit = 'Years' then 'year'
                when TermUnit = 'Months' then 'month'
                else 'day' -- assumes default 'day' unit
                end AS TermUnitRenamed
        from GIBDataWarehouse.dbo.vw_Positions
) x

Of course, you could just apply the CASE and DATEADD logic in original query itself and avoid nesting altogether if you don’t need TermUnitRenamed for anything else.

select
    PositionDate
    , ISIN
    , Issuer
    , FirstCouponDate
    , TermLength
    , TermUnit
    , case
        when TermUnit = 'Years' then DateAdd(year, TermLength, FirstCouponDate) 
        when TermUnit = 'Months' then DateAdd(month, TermLength, FirstCouponDate) 
        else then DateAdd(day, TermLength, FirstCouponDate) -- assumes default 'day' unit
        end AS newDate
from GIBDataWarehouse.dbo.vw_Positions