CODEWITHSUNDEEP
sqlpostgresql
Jake Wagner
Jake Wagner

Reputation: 826

Fill in Missing Values in Query

Say we have this dataset: Two students are enrolled in two different courses and attendance is taken for each student. I am interested in finding the attendance_dates for the students for which either one of them or both of them missed the respective subject. For the subject and the attendance_dates the students did not show up return NULL. For example: Chris missed English class on 2/5/2021 and both Chris and Joseph missed Chemistry and English on 2/6/2021.

I am using postgreSQL!

subject            attendance_dates        student 
Chemistry             2/4/2021              Chris
Chemistry             2/4/2021              Joseph
English               2/4/2021              Chris
English               2/4/2021              Joseph
Chemistry             2/5/2021              Chris
Chemistry             2/5/2021              Joseph
English               2/5/2021              Joseph
Chemistry             2/7/2021              Chris
Chemistry             2/7/2021              Joseph
English               2/7/2021              Chris
English               2/7/2021              Joseph

What I have tried:

with cte as (
select subject,attendance_dates,student from my_table
)
,
dates as (
select * from generate_series('2021-02-04', '2021-02-07', interval '1 day')  AS more_dates
)

select subject_id,cast(more_dates as date)more_dates,student
from cte c
right join dates d 
on cast(more_date as date) = attendance_dates

Result:

subject            attendance_dates        student 
Chemistry             2/4/2021              Chris
Chemistry             2/4/2021              Joseph
English               2/4/2021              Chris
English               2/4/2021              Joseph
Chemistry             2/5/2021              Chris
Chemistry             2/5/2021              Joseph
English               2/5/2021              Joseph
                      2/6/2021                           <----------
Chemistry             2/7/2021              Chris
Chemistry             2/7/2021              Joseph
English               2/7/2021              Chris
English               2/7/2021              Joseph

Requested:

subject           attendance_dates          student 
Chemistry             2/4/2021               Chris
Chemistry             2/4/2021               Joseph
English               2/4/2021               Chris
English               2/4/2021               Joseph
Chemistry             2/5/2021               Chris
Chemistry             2/5/2021               Joseph
English               2/5/2021                         <------------
English               2/5/2021               Joseph
Chemistry             2/6/2021                         <-----------
Chemistry             2/6/2021                         <-----------
English               2/6/2021                         <-----------
English               2/6/2021                         <-----------
Chemistry             2/7/2021               Chris
Chemistry             2/7/2021               Joseph
English               2/7/2021               Chris
English               2/7/2021               Joseph

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I think you really want a flag on each day for each student and subject indicating whether or not the student attended.

Use cross join to create a row for all dates, subjects, and students. Then bring in an flag to indicate whether the student attended on that day:

select d.date, su.subject, st.student,
       (t.student is not null) as attended_flag
from generate_series('2021-02-04', '2021-02-07', interval '1 day') d(dte) cross join
     (select distinct subject from my_table) su cross join
     (select distinct student from my_table) st left join
     my_table t
     on t.student = st.student and t.subject = su.subject and
        t.attendance_date = d.dte;

Here is a db<>fiddle.

Upvotes: 2

Related Questions