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