SQLTeam.com | Weblogs | Forums

Getting students enrolled in spring and summer but not fall

#1

I am getting zero results. I am trying to find students who were enrolled in both spring and summer but have not enrolled in the fall. The code works if I run it in sections. There is something I am missing when adding the not in fall part.

select distinct p.last_name,p.First_name,A.people_id,a.graduated,ENROLL_SEPARATION,academic_year,Academic_term from academic A
join people p on p.people_id=a.PEOPLE_ID
where (academic_year='2022' and academic_term = 'SPRING' and ENROLL_SEPARATION ='Enrl') and
(academic_year='2022' and academic_term = 'Summer' and ENROLL_SEPARATION ='Enrl') and
(academic_year='2022' and academic_term = ('FALL') and ENROLL_SEPARATION <> 'ENRL') and
graduated='N'

#2

This would likely be the most efficient way to do it (although perhaps not the clearest way):


select distinct final.*
from (
    select a.people_id
    from academic a
    where (a.academic_year='2022' and academic_term IN ('SPRING', 'Summer', 'FALL') and a.ENROLL_SEPARATION ='Enrl') and
        a.graduated='N'
    group by a.people_id
    having max(case when academic_term = 'SPRING' then 1 else 0 end) = 1 and
        max(case when academic_term = 'Summer' then 1 else 0 end) = 1 and
        max(case when academic_term = 'FALL' then 1 else 0 end) = 0
) as a
join people p on p.people_id=a.PEOPLE_ID
cross apply (
    select p.last_name,p.First_name,A.people_id,'N' AS graduated, 
        'Enrl' AS ENROLL_SEPARATION, '2022' AS academic_year, 'SPRING' AS Academic_term 
    union all
    select p.last_name,p.First_name,A.people_id,'N' AS graduated, 
        'Enrl' AS ENROLL_SEPARATION, '2022' AS academic_year, 'Summer' AS Academic_term 
) as final
1 Like
#3

You should be able to infer how to pick different combinations of terms from the above code. The "HAVING MAX()..." is a nice one to know about :-). :slight_smile:

1 Like
#4

Thank you Scott! That code is what I needed.