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
Advertisement
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_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.