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.