CODEWITHSUNDEEP
sqlsql-serveralias
DaBeau96
DaBeau96

Reputation: 548

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

Upvotes: 0

Views: 60

Answers (3)

Anu
Anu

Reputation: 325

try

select SSN, FirstName,
Case when Jan is null then 0 else 1 end as Jan,
Case when Feb is null then 0 else 1 end as Feb,
Case when Mar is null then 0 else 1 end as Mar,
Case when Apr is null then 0 else 1 end as Apr,
Case when May is null then 0 else 1 end as May,
Case when Jun is null then 0 else 1 end as Jun,
Case when Jul is null then 0 else 1 end as Jul,
Case when Aug is null then 0 else 1 end as Aug,
Case when Sep is null then 0 else 1 end as Sep,
Case when Oct is null then 0 else 1 end as Oct,
Case when Nov is null then 0 else 1 end as Nov,
Case when [Dec] is null then 0 else 1 end as [Dec],
Case when ([Jan] is not null AND [Feb] is not null AND [Mar] is not null AND 
    [Apr] is not null AND [May] is not null AND [Jun] is not null AND 
    [Jul] is not null AND [Aug] is not null AND [Sep] is not null AND 
    [Oct] is not null AND [Nov] is not null AND [Dec] is not null) then 1 else 0 end as Annual
from(
Select SSN, FirstName, format(DateEnrolled, 'MMM') Enrolled
from EmployeePerson)aa 
pivot (max(Enrolled) for Enrolled in([Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec])) as dtl

Upvotes: 0

israr ahmad
israr ahmad

Reputation: 1

Try this ! SELECT SSN, FirstName, LastName, CASE WHEN (DateEnrolled > '01-01-2019' AND DateEnrolled < '01-31-2019') THEN 'Jan', CASE WHEN (DateEnrolled > '02-01-2019' AND DateEnrolled < '02-28-2019') THEN 'Feb' END AS 'Annual' FROM EmployeePerson;

I think it will be helpful.

Upvotes: 0

zip
zip

Reputation: 4061

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

Upvotes: 2

Related Questions