I want to use the Elias column startdate into anthor operation to calculate
x
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.