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 ChrisChemistry 2/4/2021 JosephEnglish 2/4/2021 ChrisEnglish 2/4/2021 JosephChemistry 2/5/2021 ChrisChemistry 2/5/2021 JosephEnglish 2/5/2021 JosephChemistry 2/7/2021 ChrisChemistry 2/7/2021 JosephEnglish 2/7/2021 ChrisEnglish 2/7/2021 JosephWhat 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,studentfrom cte cright join dates d on cast(more_date as date) = attendance_datesResult:
subject attendance_dates student Chemistry 2/4/2021 ChrisChemistry 2/4/2021 JosephEnglish 2/4/2021 ChrisEnglish 2/4/2021 JosephChemistry 2/5/2021 ChrisChemistry 2/5/2021 JosephEnglish 2/5/2021 Joseph 2/6/2021 <----------Chemistry 2/7/2021 ChrisChemistry 2/7/2021 JosephEnglish 2/7/2021 ChrisEnglish 2/7/2021 JosephRequested:
subject attendance_dates student Chemistry 2/4/2021 ChrisChemistry 2/4/2021 JosephEnglish 2/4/2021 ChrisEnglish 2/4/2021 JosephChemistry 2/5/2021 ChrisChemistry 2/5/2021 JosephEnglish 2/5/2021 <------------English 2/5/2021 JosephChemistry 2/6/2021 <-----------Chemistry 2/6/2021 <-----------English 2/6/2021 <-----------English 2/6/2021 <-----------Chemistry 2/7/2021 ChrisChemistry 2/7/2021 JosephEnglish 2/7/2021 ChrisEnglish 2/7/2021 JosephAdvertisement
Answer
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_flagfrom 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.