CODEWITHSUNDEEP
sqlpostgresql
JD Gamboa
JD Gamboa

Reputation: 382

generate_series for creating a list of months to get missing dates

I have a view with monthly attendance (date is truncated by month):

----------------------------------------------------
| Student_ID    | Date             | Attendance    |
----------------------------------------------------
| 1             | 2017-01-01       | 4             |
| 1             | 2017-02-01       | 3             |
| 1             | 2017-04-01       | 4             |
| 1             | 2017-06-01       | 2             |
| 2             | 2017-03-01       | 5             |
| 2             | 2017-05-01       | 2             |
----------------------------------------------------

As you can see, not every month appears for each ID, since this view counts and groups each date appearing in the actual attendance table. Nevertheless, I need every month to show up for each ID, even if with a 0.

----------------------------------------------------
| Student_ID    | Date             | Attendance    |
----------------------------------------------------
| 1             | 2017-01-01       | 4             |
| 1             | 2017-02-01       | 3             |
| 1             | 2017-03-01       | 0             |
| 1             | 2017-04-01       | 4             |
| 1             | 2017-05-01       | 0             |
| 1             | 2017-06-01       | 2             |
| 2             | 2017-01-01       | 0             |
| 2             | 2017-02-01       | 0             |
| 2             | 2017-03-01       | 5             |
| 1             | 2017-04-01       | 0             |
| 2             | 2017-05-01       | 2             |
| 1             | 2017-06-01       | 0             |
----------------------------------------------------

I tried unsuccesfully calling this view and doing a full join with

SELECT
    CAST(CAST('2017-01-01' AS DATE) + (interval '1' month * generate_series(0,11)) AS DATE) AS month,
    0 AS attendance

But it doesn't work, yet I feel somewhat close to the actual solution. Help appreciated.

Upvotes: 1

Views: 299

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Generate all combinations of dates per student_id with a cross join and then left join the original table on that to get missing rows with 0 values.

select i.student_id,m.mth,coalesce(t.attendance,0)
from (select distinct student_id from tbl) i
cross join generate_series('2017-01-01'::date,'2017-12-01'::date, '1 MONTH') m(mth)
left join tbl t on t.student_id=i.student_id and t.dt=m.mth

Upvotes: 3

Related Questions