-- Generate a series of months! (join with other time series to get 0 values):
-- { ----
SELECT * FROM generate_series('2010-01-01'::date, '2011-01-01', '1 months')
select distinct date_trunc('month', (current_date - offs)) as date
from generate_series(0,365,28) as offs
order by 1 desc
select distinct date_trunc('day', (current_date - offs)) as date
from generate_series(0,7) as offs
order by 1 desc
-- EXAMPLE:
select
date,
(case
when cnt is not null then cnt
-- when cnt is null then 0
else 0
END
) as counted
from
(
(
select count(*) as cnt,
date_trunc('day',event_date) as day
from reports_account_status_history
where 1=1
and (event = 'Closed Store' OR event = 'Uninstalled')
-- and (event = 'Installed' OR event = 'Re-opened Store')
group by 2 order by 2 desc
) A
right join
(
select distinct date_trunc('day', (current_date - (offs-2) )) as date
from generate_series(0,21) as offs
order by 1 desc
) B on A.day = B.date
) results
---- }
)