Skip to content
Advertisement

Call an alias in a case expression

I have this code that is to check each month to see if the person is enrolled in that month and at the end it is suppose to tell you if the person was enrolled for the whole year or not. The Annual is to check each month to see if they have a 1 from the case expression. The issue is I can’t get SQL to recognize the alias names like Jan and Feb

Select SSN, FirstName, LastName,
  Case (DateEnrolled > '1-1-2019' and DateEnrolled < '1-31-2019' ) then 1 else 0 as [Jan],
  Case (DateEnrolled > '2-1-2019' and DateEnrolled < '2-28-2019') then 1 else 0 as [Feb],
  ...
  Case (Jan = 1 AND Feb = 1 AND...) then 1 else 0 as [Annual]
from EmployeePerson

Advertisement

Answer

Try this:

with cte as
(
Select SSN, FirstName, LastName,
  Case (DateEnrolled > '1-1-2019' and DateEnrolled < '1-31-2019' ) then 1 else 0 end as [Jan],
  Case (DateEnrolled > '2-1-2019' and DateEnrolled < '2-28-2019') then 1 else 0 end as [Feb],
  ...
from EmployeePerson
)

select SSN, FirstName, LastName, [Jan], [Feb]...,[Dec],
Case (Jan = 1 AND Feb = 1 AND...AND [Dec] = 1) then 1 else 0 end as [Annual]
from cte 
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement