J'ai ce code qui est à vérifier chaque mois pour voir si la personne est inscrite ce mois-là et à la fin il est supposé vous dire si la personne était inscrite pour toute l'année ou non. Le Annuel
consiste à vérifier chaque mois pour voir s'ils ont un 1
à partir de l'expression de cas. Le problème est que je n'arrive pas à faire en sorte que SQL reconnaisse les noms d'alias comme Jan
et 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
3 Réponses :
Essayez ceci:
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
Merci! Cela a résolu le problème pour moi.
Essayez ceci! SELECT SSN, Prénom, Nom, CAS LORSQUE (DateEnrolled> '01-01-2019 'ET DateEnrolled <'01 -31-2019') PUIS 'Jan', CAS LORSQUE (DateEnrôlé> '02-01-2019 'ET DateEnrôlé <'02-28-2019') PUIS 'Fév' END AS 'Annuel' FROM EmployeePerson;
Je pense que ce sera utile.
Essayer
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
Il est impossible que cette requête telle qu’écrite vous donne un jour
1
pourAnnuel
, car un seul deJan
àDec
peut peut-être1
puisque leurs conditions sont mutuellement exclusives. Notez que vos conditions n'incluent pas non plus tous les jours du mois, vous devriez avoir> =
et<=
, pas>
et<