nathan-castor
4/25/2018 - 8:45 PM

Generate series of months!


-- 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


---- }

)