Skip to content
Advertisement

How to alias column into another operation

I want to use the Elias column startdate into anthor operation to calculate

CASE WHEN GETDATE() > detail.PeriodEndDate  THEN  GETDATE() 
                 ELSE detail.PeriodEndDate
        END as StartDate,

          CASE
                 WHEN usrPlan.PackagePlanId = 1  THEN  DATEADD(month, 1,  StartDate)
                 ELSE  DATEADD(YEAR, 1,  StartDate)
        END as EndDate,

Advertisement

Answer

Use CROSS APPLY to define the alias in the FROM clause. Then it is available throughout the query:

SELECT . . .,
       v.StartDate,
       (CASE WHEN up.PackagePlanId = 1 THEN  DATEADD(month, 1, v.StartDate)
             ELSE  DATEADD(YEAR, 1,  v.StartDate)
        END) as EndDate,
FROM usrPlan up . . .
     detail d . . . 
     CROSS APPLY
     (VALUES (CASE WHEN GETDATE() > d.PeriodEndDate THEN  GETDATE() 
                   ELSE d.PeriodEndDate
              END)
      ) v(StartDate)

Note that I added table aliases so the query is easier to write and read.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement