[Solved] Generate month data series with null months included?

by


You can generate all starts of months with generate_series(), then bring the table with a left join:

select 
    to_char(d.start_date, 'mon') as month,
    extract(month from d.start_date) as month_num,
    sum(cost_planned) filter (where t.aasm_state in ('open', 'planned' ) )  as planned,
    sum(cost_actual)  filter (where t.aasm_state="closed") as actual
from generate_series('2020-01-01'::date, '2020-12-01'::date, '1 month') d(start_date)
left join activity_tasks t
    on t.start_date >= d.start_date and t.start_date < d.start_date + '1 month'::interval
group by start_date
order by start_date

You can easily change the arguments of generate_series() to accomodate a different fiscal year.

6

solved Generate month data series with null months included?