Calculate column value based on another column that is calculated-CodePudding

I need to show the last date enrolled based on value that is derived in the Enrolled column. On this line below WHEN EnrolledDer = 'N' then LAST_DTE_OF_ATTEND. EnrolledDer doesn't exist because it is a derived column. It looks like I have to repeat the case statement to get EnrolledDer for the column to show LAST_DTE_OF_ATTEND for which the alias will be LastDateEnrolledDer. What is the syntax to nest that case statement and is that the best method?

If I try to reference the alias column in the case statement the error is Invalid Column named EnrolledDer'

   CASE
     WHEN EnrolledDer = 'N' then LAST_DTE_OF_ATTEND --'Invalid Column named EnrolledDer'

Sql Server 2014

SELECT [Student ID],
       [Unique Course Identifier],
       [Course Title],
       Term,
       [Section Number],
       Days,
       [Start Time],
       [End Time],
       [Start Date],
       [End Date],
       Enrolled,

      --Case, If C then Y, else N
      --TRANSACTION_STS Enrolled
      --One character code, lookup list value
        --C :Current
        --D :Dropped
        --H :History
        --P :Pre-registered
        --R :Reserved
        --W :Wait listed

       CASE 
         WHEN Enrolled = 'C' THEN 'Y'
         ELSE 'N' 
       END AS "EnrolledDer", --Enrolled Derived Column 
       --DropFlag,
       LAST_DTE_OF_ATTEND LastDateEnrolled, --Populate last date enrolled if enrolled flag above is N

       CASE
         WHEN EnrolledDer = 'N' then LAST_DTE_OF_ATTEND --'Invalid Column named EnrolledDer'
         ELSE NULL
       END AS LastDateEnrolledDer,  --LastDateEnrolledDer Derived Column

       Building,
       Room,
       ROW_NUMBER() OVER(PARTITION BY [Student ID],
                                      [Unique Course Identifier]
       ORDER BY [Start Time]) as rn
FROM cteAccScheduleFull

I got this case statement to work with a nested case. I'm not sure what the best way to indent that is. I don't like repeating code. Would another solution be to use a cte or function?

--Populate last date enrolled if enrolled flag above is N

      -- CASE
      --   WHEN EnrolledDer = 'N' then LAST_DTE_OF_ATTEND
         --ELSE NULL
      -- END AS LastDateEnrolledDer,  --LastDateEnrolledDer Derived Column

       CASE
         WHEN CASE 
                 WHEN Enrolled = 'C' THEN 'Y'
                 ELSE 'N' 
       END
        = 'N' then LAST_DTE_OF_ATTEND
         ELSE NULL
       END AS LastDateEnrolledDer,  --LastDateEnrolledDer Derived Column

CodePudding user response：

Aliasing an expression is done through cross apply. Change your FROM to:

FROM 
    cteAccScheduleFull
    cross apply
    (select CASE 
                WHEN Enrolled = 'C' THEN 'Y'
                ELSE 'N' 
            END AS EnrolledDer --Enrolled Derived Column 
    ) as q1

Then you will be able to use q1.EnrolledDer in your select any number of times. You can even chain cross apply blocks if you had more dependent expressions.