I have a table of students and classes. I would like to find what classes were dropped from one semester to another (and a similar query for classes added).

Student    Class       Semester
==============================
Alice      English     11
Alice      Geometry    11
Alice      English     12
Bob        Spanish     11
Bob        Spanish     12

My approach is to use an except (same as minus):

select distinct Class
from table
where table.Student = 'Alice'
and table.Semester = 11
except
select distinct Class
from table
where table.Student = 'Alice'
and table.Semester = 12

This works correctly, returning Geometry. However, I need to use this as a subquery like this:

select Student, string_agg(X.Class, ', ') as 'Deleted_Classes', 
count(X) as 'Num_deleted',
SemesterTable.Semester as semester, 
lag(Semester, 1) 
over (partition by StudentTable.Student 
order by SemesterTable.Semester) as Prev_Semester,
from 
StudentTable
SemesterTable
inner join (
<<<Same query from above>>>
) X on _______
where X.Num_deleted > 0

My problems is with the ____ section - inner joins can only be joined on columns that appear in the output. But my except query doesn't return the values of previous and current semesters (it might even return nothing at all if no classes were dropped). So how do I join the subquery into the main table? My desired output is:

Student     Semester     Prev Semester   Deleted_Classes
========================================================
Alice        12          11              Geometry

Alice appears because she had a change in her schedule, but Bob is omitted because there was no change in his schedule.

CodePudding user response：

I would do this via Left Join with checking the availability of the next semester for a particular student via exist in where.

Select T.Student, T.Semester 1 As Semester, T.Semester As [Prev Semester], 
       string_agg(T.Class, ',') As Deleted_Classes
From Tbl As T Left Join Tbl As T1 On (T.Student=T1.Student 
                                      And T.Semester 1=T1.Semester
                                      And T.Class=T1.Class)
Where Exists (Select * From Tbl 
              Where Student=T.Student 
                    And Semester=T.Semester 1) And
      T1.Semester Is Null
Group by T.Student, T.Semester 1, T.Semester

Result:

CodePudding user response：

with data as (
    select *,
        min(Semester) over (partition by Student, Class) as minSemester,
        max(Semester) over (partition by Student, Class) as maxSemester,
        count(*) over (partition by Student, Class) as cntSemester
    from T
    where Semester in (11, 12)
)
select Student, Class,
    case when minSemester = 12 then 'Added'   else '' end as Added,
    case when maxSemester = 11 then 'Dropped' else '' end as Dropped
from data
where maxSemester = 11;

There's all kinds of information you could derive from these values. For example, knowing that the latest semester was not 12 implies that class was dropped. You can do similar for additions.

CodePudding user response：

Using a NOT EXISTS seems appropriate for this.

create table StudentSemesters (
 Student varchar(30),
 Class varchar(30),
 Semester int
);

insert into StudentSemesters
(Student, Class, Semester) values
  ('Alice',     'English',     11) 
, ('Alice',     'Geometry',    11)
, ('Alice',     'English',     12)
, ('Bob',       'Spanish',     11) 
, ('Bob',       'Spanish',     12) 
;

select Student
, Semester 1 as [Semester] 
, Semester as [Prev_Semester]
, STRING_AGG(Class, ', ') as [Deleted_Classes] 
from StudentSemesters t
where not exists (
    select 1
    from StudentSemesters t2
    where t2.Student = t.Student
      and t2.Class = t.Class
      and t2.Semester = t.Semester 1
    )
  and exists (
    select 1
    from StudentSemesters t2
    where t2.Student = t.Student
      and t2.Semester = t.Semester 1
    )
group by Student, Semester;

select 
  Student
, Semester as [Semester] 
, Semester-1 as [Prev_Semester]
, STRING_AGG(Class, ', ') as [Added_Classes] 
from StudentSemesters t
where not exists (
    select 1
    from StudentSemesters t2
    where t2.Student = t.Student
      and t2.Class = t.Class
      and t2.Semester = t.Semester-1
    )
group by Student, Semester;

Demo on db<>fiddle here