Find missing values in the student table

user_6764969 0
Viewed 79

A student table consists of year the fees has been paid and no data for the period the fees is not paid. How to find missing year. The year are 2018 2019 2020 2021. Now the year for which the fee is not paid is to be mentioned.

Student_ID Year of paid fee
101 2018
101 2019
101 2021
102 2018
102 2021

Now for 101 fee is paid for 2018,2019,2021 but not for 2020, also for 102 fee is not paid for 2019 and 2020. So the output should be

Student_ID Year_of_Default
101 2020
102 2019,2020
join oracle sql
3 Answers

Create a CTE that returns the years that you search for and another one that returns the distinct student ids.
Cross join them and left join your table and then filter out all the matching rows.
The remaining rows contain all the years each student has not paid.
Then aggregate and use LISTAGG() to collect all the years for each student:

WITH 
  years(year) AS (
    SELECT 2018 FROM dual UNION ALL
    SELECT 2019 FROM dual UNION ALL
    SELECT 2020 FROM dual UNION ALL
    SELECT 2021 FROM dual
  ),
  students(Student_ID) AS (SELECT DISTINCT Student_ID FROM tablename)
SELECT s.Student_ID,
       LISTAGG(y.year, ',') WITHIN GROUP (ORDER BY y.year) Year_of_Default
FROM years y CROSS JOIN students s
LEFT JOIN tablename t
ON t.Year_of_paid_fee = y.year AND t.Student_ID = s.Student_ID
WHERE t.Student_ID IS NULL
GROUP BY s.Student_ID

See the demo.

user_104988280

If your Oracle version is 19c or later then use Query#1 or for older version use Query#2.

 CREATE TABLE mytable (Student_ID int, Year_of_paid_fee int);
 
 Insert INTO mytable VALUES (101, 2018);
 Insert INTO mytable VALUES (101, 2019);
 Insert INTO mytable VALUES (101, 2021);
 Insert INTO mytable VALUES (102, 2018);
 Insert INTO mytable VALUES (102, 2021);

Query#1 (For Oracle 19c and later)

 with allyears as
 (
     select 2018 yr from dual
     union all
     select 2019 from dual
     union all
     select 2020 from dual
     union all
     select 2021 from dual
 )
 
 select  mt.student_id , LISTAGG(distinct yr, ',') WITHIN GROUP (ORDER BY yr)  Year_of_Default
 from mytable mt cross join allyears 
 where not exists(select * from mytable m WHERE  mt.student_id=m.student_id and yr=m.YEAR_OF_PAID_FEE) 
 group by mt.student_id

Query#2 (For older version of oracle than 19c)

 with allyears as
 (
     select 2018 yr from dual
     union all
     select 2019 from dual
     union all
     select 2020 from dual
     union all
     select 2021 from dual
 )
 ,cte as 
 (
    select distinct  mt.student_id ,  yr
    from mytable mt cross join allyears 
    where not exists(select * from mytable m WHERE  mt.student_id=m.student_id and yr=m.YEAR_OF_PAID_FEE) 
 )
 select student_id, LISTAGG(yr,',') WITHIN GROUP (ORDER BY yr)  Year_of_Default
 from cte 
 group by student_id

Output:

STUDENT_ID YEAR_OF_DEFAULT
101 2020
102 2019,2020

db<fiddle here

user_86516010

One option would be using MINUS operator in order to subtract the year values of the table from the set of years (2018..2021) for each student such as

SELECT Student_ID, 
       LISTAGG(Year,',') WITHIN GROUP (ORDER BY Student_ID) AS Year_of_Default
  FROM (
         SELECT DISTINCT Student_ID, level + 2017 AS Year
           FROM t
        CONNECT BY level <= 4 
          MINUS
         SELECT Student_ID,Year
           FROM t
        )
  GROUP BY Student_ID

Demo

user_58413060
Related